计数和数学表达时的嵌套情况

发布于 2025-01-26 00:48:31 字数 593 浏览 1 评论 0原文

如何在下面达到此结果？

ID	id_status	速率
25	x	62.5％
15	y	37.5％

尝试了

SELECT 
  COUNT(tab.id) AS id,
  tab.status AS id_status,
  (CASE 
     WHEN tab.status = 'X' THEN (25/40) * 100 -- this is where I'm stucked (40 = total of ids)
     WHEN tab.status = 'Y' THEN 100 - ((25/40) * 100)
     END AS rate
FROM table AS tab
WHERE tab.status in ('X', 'Y')
GROUP BY ROLLUP (tab.status)

原文

how can I achieve this result below?

id	id_status	rate
25	X	62.5%
15	Y	37.5%

having tried this

SELECT 
  COUNT(tab.id) AS id,
  tab.status AS id_status,
  (CASE 
     WHEN tab.status = 'X' THEN (25/40) * 100 -- this is where I'm stucked (40 = total of ids)
     WHEN tab.status = 'Y' THEN 100 - ((25/40) * 100)
     END AS rate
FROM table AS tab
WHERE tab.status in ('X', 'Y')
GROUP BY ROLLUP (tab.status)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

热情消退 2025-02-02 00:48:31

您可以使用窗口函数获取总数

select count(tab.id) as id,
      tab.status as id_status,
      200.0 * count(tab.id) / sum(count(*)) over(order by status rows  between unbounded preceding and unbounded following) as rate    
from your_table tab
where tab.status in ('X', 'Y')
group by rollup(tab.status)

注释明确的窗口规范，因为默认值通常为..和当前行和200，因为lollup将添加总行。

You can use a window function to get total count

select count(tab.id) as id,
      tab.status as id_status,
      200.0 * count(tab.id) / sum(count(*)) over(order by status rows  between unbounded preceding and unbounded following) as rate    
from your_table tab
where tab.status in ('X', 'Y')
group by rollup(tab.status)

Note explicit window specification because the default is generally .. and current row and 200 because rollup will add the total row.

db<>fiddle

回复收藏 0 原文

~没有更多了~