动态按用户id和日期范围sql分组查询结果

Question

我有一个查询在特定时间范围内从不同的表中收集信息。

目前，我分别为每个用户和每个日期范围发出请求，但我想一次为所有时间范围运行它，其中时间范围是 user_opened_account_at 和 user_closed_account_at 之间的每 7 天一次，这对每个用户都是不同的.

是否有任何正确的方法可以在一个查询中执行此操作？

示例:https://www.db-fiddle.com/f/aDFuX4qjzCcUmXe8iipaBM/2

我得到的结果:

我想看到的结果:

查询:

SELECT 
    usr.id as user_id,
    usr."onboardedAt" as user_opened_account_at,
    usr."closedAt" as user_closed_account_at,
    '2021-01-01' as start_range_date,
    '2021-01-08' as end_range_date,
    tx.tx_count as tx_count,
    last_user_action.action as last_user_action
FROM "Users" usr

LEFT JOIN (
    SELECT 
        "userId",
        COUNT("id") as "tx_count"
    FROM "Transactions"
    WHERE "createdAt" >= '2021-01-01' AND "createdAt" < '2021-01-08'
    GROUP BY "userId"
) tx ON usr.id = tx."userId"

LEFT JOIN (
    SELECT "userId", "action"
    FROM "UserActions"
    WHERE "createdAt" >= '2021-01-01' AND "createdAt" < '2021-01-08'
    ORDER BY "createdAt" DESC 
    LIMIT 1
) last_user_action ON usr.id = last_user_action."userId"

WHERE usr.id = 1
ORDER BY user_id, start_range_date

架构:

CREATE TABLE "Users" (
    id bigserial PRIMARY KEY,
    "onboardedAt" timestamp with time zone,
    "closedAt" timestamp with time zone
);

CREATE TABLE "Transactions" (
    id bigserial PRIMARY KEY,
    "userId" bigint,
    "createdAt" timestamp with time zone,
    amount numeric(20,8) NOT NULL DEFAULT 0
);

CREATE TABLE "UserActions" (
    id bigserial PRIMARY KEY,
    "userId" bigint,
    "createdAt" timestamp with time zone,
    action character varying(255) NOT NULL
);


INSERT INTO "Users" ("onboardedAt", "closedAt") VALUES 
    ( '2021-01-01', '2021-02-01' ), 
    ( '2021-01-01', '2021-02-01' ), 
    ( '2021-01-01', '2021-02-01' ), 
    ( '2021-02-01', '2021-03-01' ), 
    ( '2021-02-01', '2021-03-01' );

INSERT INTO "Transactions" ("userId", "createdAt", "amount") VALUES 
    ( 1, '2021-01-02',  100 ), 
    ( 1, '2021-01-08', -100 ), 
    ( 1, '2021-01-15', -200 ),
    ( 1, '2021-01-22',  200 ),

    ( 2, '2021-01-02', -100 ), 
    ( 2, '2021-01-02',  100 ), 
    ( 2, '2021-01-15', -200 ),
    ( 2, '2021-01-16',  200 ), 

    ( 3, '2021-01-02',  100 ), 
    ( 3, '2021-01-08', -100 ), 
    ( 3, '2021-01-15', -200 ),
    ( 3, '2021-01-22',  200 ),

    ( 4, '2021-02-02',   50 ), 
    ( 4, '2021-02-08', -100 ), 
    ( 4, '2021-02-15', -200 ),
    ( 4, '2021-02-22',  200 ),

    ( 5, '2021-02-02',  200 ), 
    ( 5, '2021-02-08', -400 ), 
    ( 5, '2021-02-15', -600 ),
    ( 5, '2021-02-22',  200 );

INSERT INTO "UserActions" ("userId", "createdAt", "action") VALUES 
    ( 1, '2021-01-01', 'PLAY' ), 
    ( 1, '2021-01-01', 'PLAY' ), 
    ( 1, '2021-01-02', 'DEPOSIT' ), 
    ( 1, '2021-01-08', 'DEPOSIT' ), 
    ( 1, '2021-01-09', 'PLAY' ), 
    ( 1, '2021-01-15', 'PLAY' ), 
    ( 1, '2021-01-22', 'PLAY' ), 

    ( 2, '2021-01-01', 'PLAY' ), 
    ( 2, '2021-01-01', 'PLAY' ), 
    ( 2, '2021-01-02', 'DEPOSIT' ), 
    ( 2, '2021-01-08', 'DEPOSIT' ), 
    ( 2, '2021-01-09', 'PLAY' ), 
    ( 2, '2021-01-15', 'PLAY' ), 
    ( 2, '2021-01-22', 'PLAY' ), 

    ( 3, '2021-01-01', 'PLAY' ), 
    ( 3, '2021-01-01', 'PLAY' ), 
    ( 3, '2021-01-02', 'DEPOSIT' ), 
    ( 3, '2021-01-08', 'DEPOSIT' ), 
    ( 3, '2021-01-09', 'PLAY' ), 
    ( 3, '2021-01-15', 'PLAY' ), 
    ( 3, '2021-01-22', 'PLAY' ), 

    ( 4, '2021-02-01', 'DEPOSIT' ), 
    ( 4, '2021-02-01', 'PLAY' ), 
    ( 4, '2021-02-02', 'DEPOSIT' ), 
    ( 4, '2021-02-08', 'DEPOSIT' ), 
    ( 4, '2021-02-09', 'PLAY' ), 
    ( 4, '2021-02-15', 'PLAY' ), 
    ( 4, '2021-02-22', 'PLAY' ), 

    ( 5, '2021-02-01', 'DEPOSIT' ), 
    ( 5, '2021-02-01', 'PLAY' ), 
    ( 5, '2021-02-02', 'PLAY' ), 
    ( 5, '2021-02-08', 'PLAY' ), 
    ( 5, '2021-02-09', 'PLAY' ), 
    ( 5, '2021-02-15', 'DEPOSIT' ), 
    ( 5, '2021-02-22', 'PLAY' );

Answer 1

当然。你必须使用 LATERAL join这样您就可以在 generate_series() 表表达式中使用左表(用户)中的列值，但除此之外，它主要是您所期望的。下面是一些显示重要部分的简化 SQL，如果您想要完整的代码，请添加带有示例数据的 dbfiddle 链接。

SELECT u.user_id, week_start, count(t.transactions) tx_count
from users AS u
CROSS JOIN LATERAL generate_series(u.onboarded_at, u.account_closed_at, interval '1 week')
    AS week_start
LEFT JOIN transactions AS t
  ON t.created_at >= week_start AND
  AND t.created_at < (week_start + interval '1 week')
GROUP BY 1, 2;

请注意，这仍然主要是一个美化的 for 循环服务器端，但它几乎总是比您的代码中往返于数据库的 for 循环具有更高的性能。