在PostgreSQL中查找分组数据的比率

发布于 2025-02-06 19:05:57 字数 584 浏览 2 评论 0原文

我想在按QY和列2分组数据后找到比率。

在每个季度的比例=计算（B/（B+C））的计数中，即1-2021中的比率为2000/（2000+3000）= 0.4。

分组数据如下：

QY	第2列	计数
1-2021	A	1000
1-2021	B	2000
1-2021	C	3000
2-2021	A	5000
2-2021	B	3000
2-2021	C	4000

您可以帮助我完成Postgres中的脚本？

原文

I'd like to find ratios after grouping data by QY and Column2.

In every quarter ratio = count of( B/(B+C) ) to be calculated, i.e. in 1-2021 the ratio is 2000/(2000+3000)=0.4.

Grouped data is as follows:

QY	Column 2	Count
1-2021	A	1000
1-2021	B	2000
1-2021	C	3000
2-2021	A	5000
2-2021	B	3000
2-2021	C	4000

Could you kindly help me with the script in postgres?

分享到QQ

分享到微博

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

发布评论

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

不如归去 2025-02-13 19:05:57

使用总和和过滤器获取所需的记录。这样的事情应该可以工作：

WITH table_name(q, x, y) AS (
    SELECT *
    FROM
    (VALUES
        ('1-2021','A',  1000),
    ('1-2021','B',  2000),
    ('1-2021','C',  3000),
    ('2-2021','A',  5000),
    ('2-2021','B',  3000),
    ('2-2021','C',  4000) 
    )s
)
SELECT  q
    ,   ROUND(
            SUM(y)  FILTER(WHERE x = 'B')
            /   CAST(SUM(y) AS DECIMAL) 
        ,2) AS ratio
FROM    table_name
WHERE   x IN('B','C')
GROUP BY q
ORDER BY q;

CTE只是为了模拟现有表，仅用于测试。

Use SUM and FILTER to get the records that you need. Something like this should work:

WITH table_name(q, x, y) AS (
    SELECT *
    FROM
    (VALUES
        ('1-2021','A',  1000),
    ('1-2021','B',  2000),
    ('1-2021','C',  3000),
    ('2-2021','A',  5000),
    ('2-2021','B',  3000),
    ('2-2021','C',  4000) 
    )s
)
SELECT  q
    ,   ROUND(
            SUM(y)  FILTER(WHERE x = 'B')
            /   CAST(SUM(y) AS DECIMAL) 
        ,2) AS ratio
FROM    table_name
WHERE   x IN('B','C')
GROUP BY q
ORDER BY q;

The CTE is there just to simulate an existing table, just for testing.

回复收藏 0 原文

~没有更多了~