时间戳

首页 > 解决方案 > 无论如何要改进此 SQL 以避免多个联接

sql - 无论如何要改进此 SQL 以避免多个联接

问题描述

我想为我的 orders_summary 表中的每个产品获取 10 天的销售结果。目前我加入 orders_summary 表 10 次以获得每天的销售额。有没有更好的方法来获取这些数据？

当前的sql：

SELECT P.ID,
    P.SKU,
    FIRST_DAY.ITEMS AS ITEMS_1,
    FIRST_DAY.ORDERS AS ORDERS_1,
    SECOND_DAY.ITEMS AS ITEMS_2,
    SECOND_DAY.ORDERS AS ORDERS_2
FROM PRODUCTS AS P
LEFT JOIN
    (SELECT SKU,
            AMOUNT AS ITEMS,
            ARRAY_LENGTH(LIST,
                1) AS ORDERS
        FROM ORDERS_SUMMARY
        WHERE ORDER_DATE = TO_TIMESTAMP(1633158000000 / 1000.0)) AS FIRST_DAY ON P.SKU = FIRST_DAY.SKU
LEFT JOIN
    (SELECT SKU,
            AMOUNT AS ITEMS,
            ARRAY_LENGTH(LIST,
                1) AS ORDERS
        FROM ORDERS_SUMMARY
        WHERE ORDER_DATE = TO_TIMESTAMP(1633676400000 / 1000.0)) AS SECOND_DAY ON P.SKU = SECOND_DAY.SKU
...

结果：

在此处输入图像描述

标签： sqlpostgresql

解决方案

select main.sku, jsonb_populate_record(null::examples.table_fields, main.json_data)
from 
    (
        select t2.sku, jsonb_object_agg(t2.itemNames, t2.items) || jsonb_object_agg(t2.orderNames, t2.orders) as json_data from 
        (
            select 
                pr.sku, 
                'items' || tbl_dates.num::varchar as itemNames, 
                coalesce(sum(sOrd.amount), 0) as items,
                'orders' || tbl_dates.num::varchar as orderNames, 
                coalesce(sum(sOrd.qty), 0) as orders 
                -- tbl_dates.dates 
            from products pr
            inner join (
                select tt.num, ('2021-01-01'::date + tt.num - 1) as dates 
                from (  
                    select t.num from generate_series(1, 10, 1) AS t(num)
                ) tt 
            ) tbl_dates on true 
            left join orders_summary sOrd on sOrd.sku = pr.sku and sOrd.order_date::date = tbl_dates.dates
            group by pr.sku, tbl_dates.num, tbl_dates.dates
            order by tbl_dates.num
        ) t2 
        group by t2.sku 
    ) main;

我写了简单的选择查询，如果你想使用一个函数，那么你可以将其更改 '2021-01-01'::date为输入变量，在此代码中generate_series(1, 10, 1)你可以更改10为输入变量

推荐阅读