新手求助：如何基于日期和公司ID关联并聚合3张水果交易表

核心思路 #

要实现按date和company_id双维度统计，关键是先补全所有可能的日期-公司组合，再关联各表的汇总数据，最后用窗口函数计算累计余额。具体步骤如下：

• 先对每张采购表按date+company_id汇总每日的交易总额（单日内同公司可能有多笔记录）
• 生成包含所有日期和所有公司的全量组合（避免缺失某些日期或公司的记录）
• 左连接各汇总表到全量组合上，空值补0
• 用窗口函数按公司分组、日期排序，计算各品类的累计余额
• 最后组合出需要的total_apple_orange_balance（苹果+橙子的累计余额）

完整SQL示例 #

-- 1. 汇总各表每日每公司的交易金额
WITH apple_daily AS (
    SELECT 
        date,
        company_id,
        SUM(apple_txn_amt) AS daily_apple
    FROM apple_table
    GROUP BY date, company_id
),
orange_daily AS (
    SELECT 
        date,
        company_id,
        SUM(orange_txn_amt) AS daily_orange
    FROM orange_table
    GROUP BY date, company_id
),
pear_daily AS (
    SELECT 
        date,
        company_id,
        SUM(pear_txn_amt) AS daily_pear
    FROM pear_table
    GROUP BY date, company_id
),
-- 2. 生成所有日期-公司的全量组合
date_company_full AS (
    SELECT 
        d.date,
        c.company_id
    FROM 
        -- 替换成你的实际统计起止日期
        generate_series('2023-01-01'::date, '2023-12-31'::date, '1 day'::interval) AS d(date),
        -- 获取所有存在的公司ID
        (SELECT DISTINCT company_id FROM apple_table 
         UNION 
         SELECT DISTINCT company_id FROM orange_table 
         UNION 
         SELECT DISTINCT company_id FROM pear_table) AS c(company_id)
)
-- 3. 关联数据并计算累计余额
SELECT 
    dcf.date,
    dcf.company_id,
    -- 苹果累计余额：按公司分组，日期累加每日变动
    COALESCE(SUM(ad.daily_apple) OVER (PARTITION BY dcf.company_id ORDER BY dcf.date), 0) AS total_apple_balance,
    -- 苹果+橙子累计余额
    COALESCE(SUM(ad.daily_apple + od.daily_orange) OVER (PARTITION BY dcf.company_id ORDER BY dcf.date), 0) AS total_apple_orange_balance,
    -- 梨累计余额
    COALESCE(SUM(pd.daily_pear) OVER (PARTITION BY dcf.company_id ORDER BY dcf.date), 0) AS total_pear_balance
FROM date_company_full dcf
LEFT JOIN apple_daily ad ON dcf.date = ad.date AND dcf.company_id = ad.company_id
LEFT JOIN orange_daily od ON dcf.date = od.date AND dcf.company_id = od.company_id
LEFT JOIN pear_daily pd ON dcf.date = pd.date AND dcf.company_id = pd.company_id
ORDER BY dcf.company_id, dcf.date;

关键细节说明 #

• 全量日期-公司组合：用generate_series生成连续日期，再和所有公司ID做笛卡尔积，确保不会漏掉任何日期或公司的记录，缺失的交易金额用COALESCE补0。
• 窗口函数计算累计：PARTITION BY company_id确保每个公司单独计算累计，ORDER BY date保证按时间顺序累加每日的交易变动。
• 左连接的作用：避免因为某公司某日没有某品类交易，导致整条记录被过滤掉，保证每个日期-公司组合都有一行结果。

内容的提问来源于stack exchange，提问作者wolfrevokcats