SQL GROUP BY问题：无法生成正确的三行聚合结果求助

问题需求 #

按三个电商平台生成包含日期、平台名称、日收入总和、订单转化率的结果，原查询存在GROUP BY报错或生成大量冗余行的问题。

数据表结构与初始化数据 #

以下是三张表的创建及初始化数据：

CREATE TABLE public.products
(product_id INT4,
sku INT4 NOT NULL,
mp VARCHAR,
cost FLOAT NOT NULL DEFAULT 0,
start_date TIMESTAMP NOT NULL,
finish_date TIMESTAMP NOT NULL);

INSERT INTO public.products
(product_id, sku, mp, cost, start_date, finish_date)
VALUES
(221, 378264, 'ozon', 47.5, '2023-02-27 21:47', '2023-02-28 00:00'),
(147, 235234, 'wb', 524.22, '2023-04-02 15:23', '2023-04-10 00:00'),
(11, 124321, 'yandex', 214.0, '2023-01-04 11:02', '2023-01-11 00:00'),
(94, 145434, 'wb', 118.24, '2023-02-19 17:11', '2023-02-21 00:00'),
(543, 124432, 'yandex', 749.0, '2023-02-12 09:12', '2023-02-15 00:00'),
(45, 798623, 'ozon', 347.12, '2023-01-13 13:45', '2023-01-16 00:00'),
(47, 123486, 'ozon', 522.2, '2023-04-01 12:45', '2023-04-16 00:00'),
(89, 123135, 'yandex', 347.8, '2023-04-02 11:13', '2023-04-16 00:00');

CREATE TABLE public.events
(sku INT4 NOT NULL,
mp VARCHAR,
status VARCHAR,
quantity INT4 NOT NULL,
create_date TIMESTAMP NOT NULL);

INSERT INTO public.events
(sku, mp, status, quantity, create_date)
VALUES
(378264, 'ozon', 'заказ товара', 2, '2023-02-28 14:47'),
(378264, 'ozon', 'покупка товара', 1, '2023-02-28 19:25'),
(235234, 'wb', 'заказ товара', 10, '2023-04-03 11:10'),
(235234, 'wb', 'покупка товара', 2, '2023-04-03 19:13'),
(124321, 'yandex', 'заказ товара', 6, '2023-01-05 10:00'),
(124321, 'yandex', 'покупка товара', 2, '2023-01-05 16:17'),
(145434, 'wb', 'заказ товара', 1, '2023-02-20 14:37'),
(145434, 'wb', 'отмена', 1, '2023-02-20 15:19'),
(124432, 'yandex', 'заказ товара', 4, '2023-02-13 09:12'),
(124432, 'yandex', 'покупка товара', 4, '2023-02-13 10:22'),
(798623, 'ozon', 'заказ товара', 10, '2023-01-14 12:21'),
(798623, 'ozon', 'покупка товара', 8, '2023-01-14 13:10'),
(123486, 'ozon', 'заказ товара', 12, '2023-04-02 14:52'),
(123486, 'ozon', 'покупка товара', 3, '2023-04-02 14:33'),
(123135, 'yandex', 'заказ товара', 5, '2023-04-04 09:00'),
(123135, 'yandex', 'покупка товара', 4, '2023-04-04 09:12');

CREATE TABLE public.stock 
(product_id INT4 NOT NULL,
stock_code VARCHAR NOT NULL,
quantity INT4,
update_date TIMESTAMP NOT NULL);

INSERT INTO public.stock
(product_id, stock_code, quantity, update_date)
VALUES
(221, 'OZON_ЦЕНТРАЛЬНЫЙ_СКЛАД', 24, '2023-02-26 00:00'),
(147, 'WB_ХОРВУГИНО', 12, '2023-04-03 00:00'),
(11, 'YANDEX_ХИМКИ', 340, '2023-01-03 00:00'),
(94, 'WB_ДОМОДЕДОВО', 15, '2023-02-18 00:00'),
(543, 'YANDEX_МЯКИНИНО', 27, '2023-02-11 00:00'),
(45, 'OZON_ЧЕРТАНОВО', 74, '2023-01-12 00:00'),
(47, 'ozon', 52, '2023-04-01 00:00'),
(89, 'yandex', 9, '2023-04-01 00:00');

原查询问题分析 #

• 原CTE的ordered和sales使用逗号连接，产生笛卡尔积，导致数据重复
• RIGHT JOIN的关联逻辑混乱，关联顺序错误，进一步放大数据冗余
• 转化率计算未正确关联日期和平台，且未使用聚合函数处理，导致GROUP BY报错
• 收入计算未按日期和平台正确聚合，结果不准确

修正后的SQL查询 #

WITH daily_events AS (
    -- 按日期、平台、事件类型聚合数量
    SELECT
        DATE(create_date) AS event_date,
        mp,
        status,
        SUM(quantity) AS total_qty
    FROM public.events
    WHERE status IN ('заказ товара', 'покупка товара')
      -- 过滤当前月份数据，增加年份避免跨年度干扰
      AND DATE_PART('month', create_date) = DATE_PART('month', CURRENT_DATE)
      AND DATE_PART('year', create_date) = DATE_PART('year', CURRENT_DATE)
    GROUP BY event_date, mp, status
),
daily_metrics AS (
    -- 转换为宽表，分别获取订单数和销售数
    SELECT
        event_date,
        mp,
        COALESCE(MAX(CASE WHEN status = 'заказ товара' THEN total_qty END), 0) AS total_orders,
        COALESCE(MAX(CASE WHEN status = 'покупка товара' THEN total_qty END), 0) AS total_sales
    FROM daily_events
    GROUP BY event_date, mp
),
daily_revenue AS (
    -- 计算日收入：销售数量 * 产品成本
    SELECT
        DATE(e.create_date) AS event_date,
        e.mp,
        ROUND(SUM(e.quantity * p.cost)::DECIMAL, 2) AS day_revenue_sum
    FROM public.events e
    JOIN public.products p ON e.sku = p.sku
    WHERE e.status = 'покупка товара'
      AND DATE_PART('month', e.create_date) = DATE_PART('month', CURRENT_DATE)
      AND DATE_PART('year', e.create_date) = DATE_PART('year', CURRENT_DATE)
    GROUP BY event_date, e.mp
)
-- 关联最终结果
SELECT
    dm.event_date AS "日期",
    dm.mp AS "平台名称",
    COALESCE(dr.day_revenue_sum, 0) AS "日收入总和",
    -- 计算转化率，处理订单数为0的情况避免报错
    CONCAT(ROUND(CASE WHEN dm.total_orders = 0 THEN 0 ELSE (dm.total_sales::DECIMAL / dm.total_orders) * 100 END, 2), '%') AS "订单转化率"
FROM daily_metrics dm
LEFT JOIN daily_revenue dr ON dm.event_date = dr.event_date AND dm.mp = dr.mp
ORDER BY dm.event_date, dm.mp;

修正说明 #

1. 聚合层级统一：所有CTE都按日期（DATE(create_date)）和平台(mp)聚合，彻底避免笛卡尔积
2. 空值安全处理：用COALESCE处理没有订单或销售的情况，避免结果缺失或计算错误
3. 逻辑拆分清晰：将事件聚合、指标转换、收入计算拆分为独立CTE，便于维护和调试
4. 日期过滤完善：增加年份过滤，避免跨年度同月份数据干扰
5. 转化率安全计算：用CASE WHEN处理订单数为0的场景，避免除以0报错

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