-- 线上店铺年度销售金额、订单数量、退款金额、退款订单数、订单均价
SELECT
    s.店铺编码,
    s.年份,
    ROUND(s.销售金额,2) AS '销售金额',
    s.订单数量,
    r.退款金额,
    r.退款订单数,
    ROUND(s.销售金额/s.订单数量,2) AS '订单均价'
FROM(
    SELECT
        store_code AS '店铺编码',
        YEAR(order_date) AS '年份',
        SUM(order_settle_amt) AS '销售金额',
        -- 针对并单号情况，统计总行数加逗号个数得到总订单数量
        COUNT(platform_order_no) + 
        SUM(LENGTH(platform_order_no) - LENGTH(REPLACE(platform_order_no, ',', ''))) AS '订单数量'
    FROM
        dwd_trade_hkaudit_ecommerce_sale_mt
    WHERE
        order_date >= '2022-01-01 00:00:00'
        AND order_date <'2025-07-01 00:00:00'
    GROUP BY
        store_code,YEAR(order_date)
) s --销售
LEFT JOIN(
    SELECT
        YEAR(order_date) AS '年份',
        store_code AS '店铺编码',
        ROUND(SUM(refund.amount)+SUM(change.amount),2) AS '退款金额',
        COUNT(refund.platform_order_no) AS '退款订单数'
    FROM(
        SELECT
            -- 将拆分后的单个订单号作为分组和查询的键
            single_order_id AS single_order_no,
            MIN(store_code) AS store_code,
            MIN(order_date) AS order_date
        FROM(
            SELECT
                store_code,
                platform_order_no,
                order_date,
                splitByChar(',', platform_order_no) AS order_id_array
            FROM dwd_trade_hkaudit_ecommerce_sale_mt
            WHERE
                order_date >= '2022-01-01 00:00:00'
                AND order_date <'2025-07-01 00:00:00'
        )
        -- 使用arrayJoin将数组中的每个元素展开为一行
        ARRAY JOIN order_id_array AS single_order_id
        GROUP BY single_order_no
    ) sales
    LEFT JOIN(
        SELECT
            platform_order_no,
            SUM(return_goods_amt)
        FROM
            dwd_trade_hkaudit_ecommerce_sale_return_mt
        GROUP BY
            platform_order_no
    )  refund
        ON sales.platform_order_no = refund.platform_order_no
    LEFT JOIN(
        SELECT
            platform_order_no,
            SUM(special_barcode_amt) AS amount
        FROM
            dwd_trade_hkaudit_ecommerce_sale_change_mt
    )  change
        ON sales.platform_order_no = change.platform_order_no
    WHERE 
        refund.platform_order_no IS NOT NULL
    GROUP BY
        store_code,YEAR(order_date)
) r -- 退款
ON s.店铺编码 = r.店铺编码 AND s.年份 = r.年份
ORDER BY
    年份 ASC, 销售金额 DESC;


-- 线下店铺年度销售金额、订单数量、退款金额、退款订单数、订单均价
SELECT
    store_code as '店铺编码',
    YEAR(pay_date) as '年份',
    round(sum(CASE WHEN pay_amt > 0 THEN pay_amt ELSE 0 END), 2) as '销售金额',
    count(CASE WHEN pay_amt > 0 THEN receipt_no ELSE NULL END) as '订单数量',
    round(abs(sum(CASE WHEN pay_amt < 0 THEN pay_amt ELSE 0 END)), 2) as '退款金额',
    count(CASE WHEN pay_amt < 0 THEN receipt_no ELSE NULL END) as '退款订单数'
FROM
    dwd_trade_hkaudit_shop_receipt_pay_mt
WHERE
    pay_date >= '2022-01-01 00:00:00' 
    AND pay_date < '2025-07-01 00:00:00'
GROUP BY
    store_code, YEAR(pay_date)
ORDER BY
    store_code ASC, 年份 ASC;

-- 线上店铺月度销售金额
SELECT
    store_code as '店铺编码',
    YEAR(order_date) as '年份',
    month(order_date) as '月份',
    round(sum(order_settle_amt),2) as '销售金额'
FROM
    dwd_trade_hkaudit_ecommerce_sale_mt
WHERE
    order_date >= '2022-01-01 00:00:00'
    AND order_date <'2025-07-01 00:00:00' 
GROUP BY
    store_code,年份,月份
order BY 
    store_code,年份 asc,月份 asc

-- 线下店铺月度销售金额
SELECT
    store_code as '店铺编码',
    year(pay_date) as '年份',
    month(pay_date) as '月份',
    round(sum(CASE WHEN pay_amt > 0 THEN pay_amt ELSE 0 END), 2) as '销售金额'
FROM
    dwd_trade_hkaudit_shop_receipt_pay_mt
WHERE
    pay_date >= '2022-01-01 00:00:00'
    AND pay_date <'2025-07-01 00:00:00'
GROUP BY
    store_code,year(pay_date),month(pay_date)
order BY 
    store_code,年份 asc,月份 asc

-- 线上每年各产品数量、产品销售金额
SELECT
    goods_barcode,
    year(order_date) as '年份',
    sum(goods_qty) as '产品数量',
    order_settle_amt as '销售金额'
FROM
    dwd_trade_hkaudit_ecommerce_sale_mt
WHERE
    order_date >= '2022-01-01 00:00:00'
    AND order_date < '2025-07-01 00:00:00'
GROUP BY
    goods_barcode,year(order_date)
order BY
    goods_barcode,年份;

-- 线下每年各产品数量、产品销售金额
-- (订单号重复，根据订单号groupby后失去产品信息，小票支付表仅有订单号可关联，如何计算？)
-- 根据商品标价比例计算实际金额
SELECT 
    YEAR(b.pay_time) AS '年份',
    a.goods_barcode AS '商品条码',
    SUM(a.qty) AS '产品数量',
    ROUND(SUM(b.total_real_amt * (a.goods_amt) / b.price_tag), 2) AS '销售金额'
FROM (
    SELECT
        platform_order_no,
        goods_barcode,
        goods_amt
        price,
        qty
    FROM dwd_trade_hkaudit_shop_receipt_mt
) a
INNER JOIN(
    SELECT
        platform_order_no,
        SUM(goods_amt) as price_tag
    FROM dwd_trade_hkaudit_shop_receipt_mt
    GROUP BY platform_order_no
) b
    ON a.platform_order_no = b.platform_order_no
INNER JOIN (
    SELECT
        platform_order_no,
        SUM(pay_amt) as total_real_amt
    FROM dwd_trade_hkaudit_shop_receipt_pay_mt
    GROUP BY platform_order_no
) c ON 
    a.platform_order_no = c.platform_order_no
GROUP BY 
    YEAR(b.pay_time), a.goods_barcode
ORDER BY 
    年份, 销售金额 DESC;


-- 线上每年前五店铺金额分布
SELECT
    store_code as '店铺编码',
    sum(order_settle_amt) as '销售金额'
FROM
    dwd_trade_hkaudit_ecommerce_sale_mt
WHERE
    order_date >= '2022-01-01 00:00:00'
    AND order_date < '2023-01-01 00:00:00'
GROUP BY
    store_code
order BY
    sum(order_settle_amt) desc
LIMIT 5

-- 线下每年前五店铺金额分布
SELECT
    store_code as '店铺编码',
    round(sum(CASE WHEN pay_amt > 0 THEN pay_amt ELSE 0 END), 2) as '销售金额'
FROM
    dwd_trade_hkaudit_shop_receipt_pay_mt
WHERE
    pay_date >= '2022-01-01 00:00:00'
    AND pay_date <'2023-01-01 00:00:00'
GROUP BY
    店铺编码
order BY
    销售金额 desc
LIMIT 5

-- 线上每年各省份销售金额、订单数量
SELECT
    year(order_date) as '年份,'
    province as '省份',
    round(sum(order_settle_amt),2) as '销售金额',
    count(platform_order_no) as '订单数量'
FROM
    dwd_trade_hkaudit_ecommerce_sale_mt
WHERE
    order_date >= '2022-01-01 00:00:00'
    AND order_date < '2025-07-01 00:00:00'
GROUP BY
    年份 asc, province

-- 线下每年各店铺所在省份销售金额、订单数量
-- (如何确认店铺所在省份？)
SELECT
    store_code as '店铺编码',
    round(sum(CASE WHEN pay_amt > 0 THEN pay_amt ELSE 0 END), 2) as '销售金额'
FROM
    dwd_trade_hkaudit_shop_receipt_pay_mt
WHERE
    pay_date >= '2022-01-01 00:00:00'
    AND pay_date <'2023-01-01 00:00:00'
GROUP BY
    店铺编码
order BY
    销售金额 desc

-- 线上每年单笔订单金额分布
-- (并单如何考虑单笔金额？)
-- 单个订单号使用订单均摊金额，并单号均分金额
SELECT
    YEAR(a.order_date) AS '年份',
    a.money_area AS '单笔订单金额',
    count(a.split_item) as '订单数量',
    ROUND(sum(a.split_amount),2) AS '销售金额'
FROM(
    SELECT 
        single.split_amount,
        single.split_item,
        single.order_date,
    CASE 
        WHEN single.split_amount >0 AND single.split_amount <10 THEN '0-10'
        WHEN single.split_amount >=10 AND single.split_amount <50 THEN '10-50'
        WHEN single.split_amount >=50 AND single.split_amount <100 THEN '50-100'
        WHEN single.split_amount >=100 AND single.split_amount <500 THEN '100-500'
        WHEN single.split_amount >=500 AND single.split_amount <1000 THEN '500-1000'
        WHEN single.split_amount >=1000 AND single.split_amount <5000 THEN '1000-5000'
        WHEN single.split_amount >=5000 AND single.split_amount <10000 THEN '5000-10000'
        WHEN single.split_amount >=10000  THEN '10000及以上'
    END AS money_area
    FROM(
        SELECT
            order_date,
            split_item,
            goods_amt / length(split_array) as split_amount  -- 金额均分
        FROM (
            SELECT
                order_date,
                goods_amt,
                splitByChar(',', platform_order_no) as split_array 
            FROM 
                dwd_trade_hkaudit_ecommerce_sale_mt
            WHERE 
                order_date >='2022-01-01 00:00:00'
                AND order_date <'2025-07-01 00:00:00'
        ) single
        ARRAY JOIN split_array AS split_item
    ) 
) a
GROUP BY 
    YEAR(a.order_date),a.money_area
ORDER BY 
    年份,
    CASE a.money_area
        WHEN '0-10' THEN 1
        WHEN '10-50' THEN 2
        WHEN '50-100' THEN 3
        WHEN '100-500' THEN 4
        WHEN '500-1000' THEN 5
        WHEN '1000-5000' THEN 6
        WHEN '5000-10000' THEN 7
        WHEN '10000及以上' THEN 8
    END;

-- 线下每年单笔订单金额分布
SELECT
    YEAR(order_date) AS '年份',
    a.money_area AS '单笔订单金额',
    count(a.platform_order_no) as '订单数量',
    ROUND(sum(a.order_settle_amt),2) AS '销售金额'
FROM(
    SELECT 
        order_settle_amt,
        platform_order_no,
    CASE 
        WHEN pay_amt >0 AND pay_amt <10 THEN '0-10'
        WHEN pay_amt >=10 AND pay_amt <50 THEN '10-50'
        WHEN pay_amt >=50 AND pay_amt <100 THEN '50-100'
        WHEN pay_amt >=100 AND pay_amt <500 THEN '100-500'
        WHEN pay_amt >=500 AND pay_amt <1000 THEN '500-1000'
        WHEN pay_amt >=1000 AND pay_amt <5000 THEN '1000-5000'
        WHEN pay_amt >=5000 AND pay_amt <10000 THEN '5000-10000'
        WHEN pay_amt >=10000  THEN '10000及以上'
    END AS money_area
    FROM 
        dwd_trade_hkaudit_shop_receipt_pay_mt 
    WHERE
        order_date >='2022-01-01 00:00:00' 
        AND order_date <'2025-07-01 00:00:00'
    ) a
GROUP BY 
    a.money_area
ORDER BY 
    年份,
    CASE a.money_area
        WHEN '0-10' THEN 1
        WHEN '10-50' THEN 2
        WHEN '50-100' THEN 3
        WHEN '100-500' THEN 4
        WHEN '500-1000' THEN 5
        WHEN '1000-5000' THEN 6
        WHEN '5000-10000' THEN 7
        WHEN '10000及以上' THEN 8
    END;

-- 线上每年同一个收货地址销售金额、订单数量(需考虑脱敏情况)
SELECT
    地址
    round(sum(sales.order_settle_amt),2) as '销售金额',
    COUNT(sales.platform_order_no) + 
    SUM(LENGTH(sales.platform_order_no) - LENGTH(REPLACE(sales.platform_order_no, ',', ''))) as '订单数量'
FROM
    dwd_trade_hkaudit_ecommerce_sale_mt
WHERE 
    order_date >='2022-01-01 00:00:00' 
    AND order_date <'2025-07-01 00:00:00'
GROUP BY
    地址

-- 线上每年各天和各小时订单数和销售金额
-- 各天
SELECT
    year(order_date) as '年份',
    date(order_date) as '日期',
    COUNT(sales.platform_order_no) + 
    SUM(LENGTH(sales.platform_order_no) - LENGTH(REPLACE(sales.platform_order_no, ',', ''))) as '订单数量',
    ROUND(SUM(final_amount),2) AS '销售金额'
FROM
    dwd_trade_hkaudit_ecommerce_sale_mt
WHERE 
    order_date >='2022-01-01 00:00:00' 
    AND order_date <'2025-07-01 00:00:00'
GROUP BY
    year(order_date),date(order_date)
order BY
    年份, 日期;
-- 各小时
SELECT
    year(order_date) as '年份',
    CONCAT(HOUR(order_date), '点到', HOUR(order_date) + 1, '点') as '时间',
    COUNT(platform_order_no) + 
    SUM(LENGTH(platform_order_no) - LENGTH(REPLACE(platform_order_no, ',', ''))) as '订单数量',
    ROUND(SUM(order_settle_amt),2) AS '销售金额'
FROM
    dwd_trade_hkaudit_ecommerce_sale_mt
WHERE 
    order_date >='2022-01-01 00:00:00' 
    AND order_date <'2025-07-01 00:00:00'
GROUP BY
    year(order_date), HOUR(order_date)
order BY
    年份, 时间;

-- 线下每年各天和各小时订单数和销售金额
-- 各天
SELECT
    year(pay.pay_time) as '年份',
    date(pay.pay_time) as '日期',
    sum(order_num.num) as '订单数量',
    round(sum(pay.amount),2) as '销售金额'
FROM(
    SELECT
        platform_order_no,
        max(pay_date) as pay_time,
        round(sum(CASE WHEN pay_amt > 0 THEN pay_amt ELSE 0 END), 2) as amount
    FROM 
        dwd_trade_hkaudit_shop_receipt_pay_mt
    WHERE 
        pay_date >='2022-01-01 00:00:00' 
        AND pay_date <'2025-07-01 00:00:00'
    GROUP BY
        platform_order_no
) pay
left join(
    SELECT
        platform_order_no,
        count(platform_order_no) as num
    FROM
        dwd_trade_hkaudit_shop_receipt_mt
    GROUP BY
        platform_order_no
) order_num
on 
    pay.platform_order_no = order_num.platform_order_no
GROUP BY
    year(pay.pay_time),date(pay.pay_time)
order BY
    年份,日期;

-- 各小时
SELECT
    YEAR(pay.pay_time) as '年份',
    CONCAT(HOUR(pay.pay_time), '点到', HOUR(pay.pay_time) + 1, '点') as '时间',
    SUM(order_num.num) as '订单数量',
    ROUND(SUM(pay.amount), 2) as '销售金额'
FROM (
    SELECT
        platform_order_no,
        MAX(pay_date) as pay_time,
        sum(CASE WHEN pay_amt > 0 THEN pay_amt ELSE 0 END) as amount
    FROM 
        dwd_trade_hkaudit_shop_receipt_pay_mt
    WHERE 
        pay_date >= '2022-01-01 00:00:00' 
        AND pay_date < '2025-07-01 00:00:00'
    GROUP BY 
        platform_order_no
) pay
LEFT JOIN (
    SELECT
        platform_order_no,
        COUNT(platform_order_no) as num
    FROM 
        dwd_trade_hkaudit_shop_receipt_mt
    GROUP BY 
        platform_order_no
) order_num 
ON 
    pay.platform_order_no = order_num.platform_order_no
GROUP BY 
    YEAR(pay.pay_time), HOUR(pay.pay_time)
ORDER BY 
    年份, 时间;

-- 线上每年大促期间店铺销售金额、订单数量
SELECT
    store_code as '店铺编码',
    YEAR(order_date) AS '年份',
    CASE 
        WHEN (MONTH(order_date) = 6 AND DAY(order_date) BETWEEN 15 AND 21) THEN '父亲节'
        WHEN (MONTH(order_date) = 6 AND DAY(order_date) BETWEEN 10 AND 20) THEN '618'
        WHEN (MONTH(order_date) = 11 AND DAY(order_date) BETWEEN 1 AND 15) THEN '双11'
        ELSE '其他'
    END as '大促类型',
    ROUND(SUM(order_settle_amt), 2) as '销售金额',
    COUNT(platform_order_no) + 
    SUM(LENGTH(platform_order_no) - LENGTH(REPLACE(platform_order_no, ',', ''))) as '订单数量'
FROM dwd_trade_hkaudit_ecommerce_sale_mt
WHERE order_date >= '2022-01-01 00:00:00'
    AND order_date < '2025-07-31 00:00:00'
    AND (
        (MONTH(order_date) = 6 AND DAY(order_date) BETWEEN 15 AND 21) OR  -- 父亲节
        (MONTH(order_date) = 6 AND DAY(order_date) BETWEEN 10 AND 20) OR  -- 618
        (MONTH(order_date) = 11 AND DAY(order_date) BETWEEN 1 AND 15)     -- 双11
    )
GROUP BY store_code, YEAR(order_date), 大促类型
ORDER BY store_code ASC, 年份 ASC, 
    CASE 
        WHEN '大促类型' = '父亲节' THEN 1
        WHEN '大促类型' = '618' THEN 2
        WHEN '大促类型' = '双11' THEN 3
    END;

-- 线下每年大促期间店铺销售金额、订单数量
SELECT
    year(pay_time) as '年份',
    pay.store as '店铺编码',
    CASE 
        WHEN (MONTH(pay.pay_time) = 6 AND DAY(pay.pay_time) BETWEEN 15 AND 21) THEN '父亲节'
        WHEN (MONTH(pay.pay_time) = 6 AND DAY(pay.pay_time) BETWEEN 10 AND 20) THEN '618'
        WHEN (MONTH(pay.pay_time) = 11 AND DAY(pay.pay_time) BETWEEN 1 AND 15) THEN '双11'
        ELSE '其他'
    END as '大促类型',
    ROUND(SUM(pay.amount), 2) as '销售金额',
    sum(order_num.num) as '订单数量'
FROM(
    SELECT
        platform_order_no,
        max(store_code) as store,
        max(pay_date) as pay_time,
        sum(CASE WHEN pay_amt > 0 THEN pay_amt ELSE 0 END) as amount
    FROM 
        dwd_trade_hkaudit_shop_receipt_pay_mt
    WHERE 
        pay_date >='2022-01-01 00:00:00' 
        AND pay_date <'2025-07-01 00:00:00'
        AND (
        (MONTH(pay_date) = 6 AND DAY(pay_date) BETWEEN 15 AND 21) OR  -- 父亲节
        (MONTH(pay_date) = 6 AND DAY(pay_date) BETWEEN 10 AND 20) OR  -- 618
        (MONTH(pay_date) = 11 AND DAY(pay_date) BETWEEN 1 AND 15)     -- 双11
    )
    GROUP BY
        platform_order_no
) pay
left join(
    SELECT
        platform_order_no,
        count(platform_order_no) as num
    FROM
        dwd_trade_hkaudit_shop_receipt_mt
    GROUP BY
        platform_order_no
) order_num
on 
    pay.platform_order_no = order_num.platform_order_no
GROUP BY store_code, YEAR(order_date), 大促类型
ORDER BY store_code, 年份, 
    CASE 
        WHEN '大促类型' = '父亲节' THEN 1
        WHEN '大促类型' = '618' THEN 2
        WHEN '大促类型' = '双11' THEN 3
    END;


-- 线上每年订单下单和发货间隔的销售金额、订单数量分布
-- (并单如何考虑下单时间？)
-- 视为同一时间下单
SELECT
    CASE timestampdiff(day,deliver_date,order_date)
        WHEN 0 THEN '当天'
        WHEN 1 THEN '1天'
        WHEN 2 THEN '2天'
        WHEN 3 THEN '3天'
        WHEN 4 THEN '4天'
        ELSE '5天及以上'
    END AS '发货间隔',
    SUM(order_settle_amt) AS '销售金额',
    COUNT(platform_order_no) + 
    SUM(LENGTH(platform_order_no) - LENGTH(REPLACE(platform_order_no, ',', ''))) AS '订单数量'
FROM
    dwd_trade_hkaudit_ecommerce_sale_mt sales
WHERE
    order_date >= '2022-01-01 00:00:00'
    AND order_date < '2025-07-31 00:00:00'
GROUP BY
    发货间隔
ORDER BY
    CASE 发货间隔
        WHEN '当天' THEN 1
        WHEN '1天' THEN 2
        WHEN '2天' THEN 3
        WHEN '3天' THEN 4
        WHEN '4天' THEN 5
        WHEN '5天及以上' THEN 6
    END;


-- 线上每年各物流承运商销售金额、订单数量
SELECT
    year(sales.order_date) AS '年份',
    carrier as '物流承运商',
    round(sum(sales.order_settle_amt),2) as '销售金额',
    COUNT(sales.platform_order_no) + 
    SUM(LENGTH(sales.platform_order_no) - LENGTH(REPLACE(sales.platform_order_no, ',', ''))) as '订单数量'
FROM
    dwd_trade_hkaudit_ecommerce_sale_mt sales
WHERE  
    order_date >= '2022-01-01 00:00:00'
    AND order_date < '2025-07-31 00:00:00'
GROUP BY
    year(sales.order_date), carrier
order BY 年份, carrier;

-- 线上每年退款大于原销售的差异金额、订单数量
SELECT
    store_code AS '店铺编码',
    YEAR(order_date) AS '年份',
    ROUND(SUM(excess_refund), 2) AS '差异金额',
    COUNT(*) AS '订单数量'
FROM (
    SELECT
        s.store_code,
        s.order_date,
        s.order_settle_amt AS sale_amt,
        COALESCE(r.return_amt, 0) + COALESCE(c.change_amt, 0) AS refund_amt,
        CASE 
            WHEN (COALESCE(r.return_amt, 0) + COALESCE(c.change_amt, 0)) > ABS(s.order_settle_amt) 
            THEN (COALESCE(r.return_amt, 0) + COALESCE(c.change_amt, 0)) - ABS(s.order_settle_amt)
            ELSE 0
        END AS excess_refund
    FROM dwd_trade_hkaudit_ecommerce_sale_mt s
    LEFT JOIN (
        SELECT platform_order_no, SUM(return_goods_amt) AS return_amt
        FROM dwd_trade_hkaudit_ecommerce_sale_return_mt
        GROUP BY platform_order_no
    ) r ON s.platform_order_no = r.platform_order_no
    LEFT JOIN (
        SELECT platform_order_no, SUM(special_barcode_amt) AS change_amt
        FROM dwd_trade_hkaudit_ecommerce_sale_change_mt
        GROUP BY platform_order_no
    ) c ON s.platform_order_no = c.platform_order_no
    WHERE (r.return_amt IS NOT NULL OR c.change_amt IS NOT NULL)
        AND s.order_date >= '2022-01-01 00:00:00'
        AND s.order_date < '2025-07-01 00:00:00'
) excess_data
WHERE excess_refund > 0
GROUP BY store_code, YEAR(order_date)
ORDER BY 年份, 超额退款总金额 DESC;


-- 线下每年退款大于原销售的差异金额、订单数量
SELECT 
    YEAR(pay_time) AS '年份',
    SUM(total_refund - total_sale) AS '差异金额',
    COUNT(*) AS '订单数量'
FROM (
    SELECT 
        receipt_no,
        SUM(CASE WHEN pay_amt > 0 THEN pay_amt ELSE 0 END) AS total_sale,
        SUM(CASE WHEN pay_amt < 0 THEN ABS(pay_amt) ELSE 0 END) AS total_refund,
        MAX(pay_date) AS pay_time
    FROM dwd_trade_hkaudit_shop_receipt_pay_mt
    GROUP BY receipt_no
    HAVING total_refund > total_sale
)
GROUP BY YEAR(pay_time);