sql - Postgresql 查看聚合数据

Question

我遇到了一个问题，要使它“漂亮”、高效且易于阅读。不幸的是，它缺少这两个属性。

给定一个包含 date,transaction_type,username 和 credits 的表，我想生成一个 View ，该 View 汇总为以下字段:date_from,date_next,username, credits_total，credits_total

解释:

• date_from 和 date_to 是这个月的第一天和下一个月的第一天。 (例如 2022-06-01 和 2022-07-01)
• 用户名已分组，因此每个 date_from/date_next 对只有一个相同的用户名
• credits_total 是 credit_change 的总和，其中 transaction = 'charge'
• credits_left 是 credits_total - sum(credits_change where transaction_type = 'usage')

我发现了多个问题并且能够部分解决它们:

• date_from/_next 使用 date_trunc('month', date) 和 `date_trunc('month', date) + interval '1 month'' 很容易
• 可以使用 group by 对用户名/日期进行分组
• 不重复地制作 credits_total 是很困难的。还是子查询是唯一的解决方案？
• 剩余的积分几乎相同，但使用其他 transaction_type 并从 credits_total 中减去。我如何重复使用 credits_total？

我想出了什么(并且非常不满意)

源表:

create table usage  -- simplified
(
    datetime timestamp default now() not null,
    transaction_type varchar(16) not null,
    user varchar(128) not null,
    credits_change int not null,
);

我的 View 代码:

CREATE MATERIALIZED VIEW token_usage
AS
SELECT 
       -- trivial:
       user,
       date_trunc('month', datetime) as date_from,
       date_trunc('month', datetime) + interval '1 month' as date_next,

       -- sum of credits_change with requirement + duplication
       (    -- see here. first time
            SELECT sum(credits_change)
            FROM usage
            WHERE transaction_type = 'charge'
            AND datetime BETWEEN date_trunc('month', datetime) AND date_trunc('month', datetime) + interval '1 month'
       ) as credits_total,

       -- sum of credits change minus other sum and more duplication
       (   -- see here. using the same again
           SELECT sum(credits_change)
           FROM usage
           WHERE transaction_type = 'charge'
           AND datetime BETWEEN date_trunc('month', datetime) AND date_trunc('month', datetime) + interval '1 month'
       ) - (  -- see here. using the same again, but with different transaction_type
           SELECT sum(credits_change)
           FROM usage
           WHERE transaction_type = 'usage'
           AND datetime BETWEEN date_trunc('month', datetime) AND date_trunc('month', datetime) + interval '1 month'
       ) as credits_left
    FROM usage
    GROUP BY user_name, datetime, datetime_next_start
WITH DATA;

看来我只是缺少一些 postgresql 工具，以使其变得更好。

感谢您的帮助:)

Answer 1

在不知道一些示例数据和尝试查询的预期输出的情况下，以下肯定可以作为您完整解决方案的草图。我想，这里的重点是了解聚合函数的 FILTER 子句 (*):

CREATE MATERIALIZED VIEW token_usage AS

SELECT
    user,
    date_trunc('month', datetime) as date_from,
    date_trunc('month', datetime) + interval '1 month' as date_next,
    
    SUM(credits_change) FILTER (WHERE transaction_type = 'charge') as credits_total,
    SUM(credits_change) FILTER (WHERE transaction_type = 'charge')
        - SUM(credits_change) FILTER (WHERE transaction_type = 'usage') as credits_left
FROM usage
GROUP BY 1, 2, 3

具有较少重复但可能由于子查询而导致可读性较差的替代方案:

CREATE MATERIALIZED VIEW token_usage AS

SELECT 
    user,
    date_from,
    date_from + interval '1 month' as date_next,
    credits_total,
    credits_total - credits_usage as credits_left
FROM (
    SELECT
        user,
        date_trunc('month', datetime) as date_from,
    
        SUM(credits_change) FILTER (WHERE transaction_type = 'charge') as credits_total,
        SUM(credits_change) FILTER (WHERE transaction_type = 'usage') as credits_usage
    FROM usage
    GROUP BY 1, 2 
) s

*) 您也可以使用 CASE 子句代替 FILTER:

SUM(abc) FILTER (WHERE condition)

-- generally the same as

SUM(
    CASE WHEN condition THEN 
        abc 
    END
)