如何合并不同GROUP BY子句的查询结果，实现库存出入日期统计？

核心思路 #

要同时统计每个日期下各库存类型的入库、出库数量，需先拆分出入库日期维度，将所有日期与库存类型做关联，再分别匹配出入库的统计结果，避免直接按in_date和out_date分组导致的维度冲突。

针对HeidiSQL（MySQL）的实现 #

-- 获取所有库存类型
WITH stock_types AS (
    SELECT DISTINCT stock_type FROM records
),
-- 生成指定统计区间内的所有日期
dates AS (
    SELECT date_add('2022-11-10', INTERVAL n DAY) AS stat_date
    FROM (
        SELECT t4.n*1000 + t3.n*100 + t2.n*10 + t1.n AS n
        FROM (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
             (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
             (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t3,
             (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t4
    ) num
    WHERE date_add('2022-11-10', INTERVAL n DAY) <= '2022-12-08'
),
-- 统计每个日期的入库总数
in_stats AS (
    SELECT in_date AS stat_date, COUNT(*) AS in_count
    FROM records
    WHERE in_date BETWEEN '2022-11-10' AND '2022-12-08'
    GROUP BY in_date
),
-- 统计每个日期的出库总数
out_stats AS (
    SELECT out_date AS stat_date, COUNT(*) AS out_count
    FROM records
    WHERE out_date BETWEEN '2022-11-10' AND '2022-12-08'
    GROUP BY out_date
)
-- 关联所有维度得到最终结果
SELECT 
    d.stat_date AS date,
    COALESCE(i.in_count, 0) AS in_count,
    COALESCE(o.out_count, 0) AS out_count,
    s.stock_type
FROM dates d
CROSS JOIN stock_types s
LEFT JOIN in_stats i ON d.stat_date = i.stat_date
LEFT JOIN out_stats o ON d.stat_date = o.stat_date
-- 仅保留有出入库记录的日期（可按需删除此条件）
WHERE i.in_count IS NOT NULL OR o.out_count IS NOT NULL
ORDER BY d.stat_date, s.stock_type;

针对BigQuery的实现 #

BigQuery支持更简洁的日期生成逻辑，实现如下：

WITH stock_types AS (
    SELECT DISTINCT stock_type FROM `your-project.your-dataset.records`
),
dates AS (
    SELECT date AS stat_date
    FROM UNNEST(GENERATE_DATE_ARRAY('2022-11-10', '2022-12-08', INTERVAL 1 DAY)) AS date
),
in_stats AS (
    SELECT in_date AS stat_date, COUNT(*) AS in_count
    FROM `your-project.your-dataset.records`
    WHERE in_date BETWEEN '2022-11-10' AND '2022-12-08'
    GROUP BY in_date
),
out_stats AS (
    SELECT out_date AS stat_date, COUNT(*) AS out_count
    FROM `your-project.your-dataset.records`
    WHERE out_date BETWEEN '2022-11-10' AND '2022-12-08'
    GROUP BY out_date
)
SELECT 
    d.stat_date AS date,
    COALESCE(i.in_count, 0) AS in_count,
    COALESCE(o.out_count, 0) AS out_count,
    s.stock_type
FROM dates d
CROSS JOIN stock_types s
LEFT JOIN in_stats i ON d.stat_date = i.stat_date
LEFT JOIN out_stats o ON d.stat_date = o.stat_date
WHERE i.in_count IS NOT NULL OR o.out_count IS NOT NULL
ORDER BY d.stat_date, s.stock_type;

可选调整 #

如果只需保留存在出入库记录的日期（而非整个区间的所有日期），可将dates CTE替换为：

dates AS (
    SELECT in_date AS stat_date FROM records
    UNION
    SELECT out_date AS stat_date FROM records
    WHERE out_date BETWEEN '2022-11-10' AND '2022-12-08'
)

若示例中的in_count/out_count并非单纯计数（如按实际库存数量统计），只需将COUNT(*)替换为对应的业务逻辑（比如SUM(quantity)，若存在数量字段）。

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