周边数据分析#

周边产品销售/报货/门店流水 CSV → Python 汇总合并 → MySQL
→ Excel Power Query（ODBC）关联门店主数据 → 报表

SELECT
    stat_shop_id AS 门店编号,
    item_name AS 周边产品名称,
    LEFT(business_date, 6) AS 月份,
    SUM(dp_item_count) AS 周边产品销量,
    SUM(dp_total_amount) AS 周边产品流水,
    -- 严格判断：只有当日销量大于 0 时，才计入售卖天数
    COUNT(DISTINCT CASE WHEN dp_item_count > 0 THEN business_date END) AS 产品售卖天数
FROM ads_dbs_trade_food_di
WHERE food_category_name IN ('周边产品', '周边')
  AND business_date >= '20250101' AND business_date < '20250201'
GROUP BY stat_shop_id, item_name, LEFT(business_date, 6)
HAVING SUM(dp_item_count) > 0;

WITH report_order AS (
    SELECT
        id AS `订单ID`, order_num AS `订单编号`, order_status AS `订单状态`,
        order_time AS `订单时间`, order_type AS `订单类型`,
        order_notes AS `订单备注`, store_code AS `门店编号`
    FROM dwd_rps_tll_order_di
),
unique_orders AS (
    SELECT
        `订单ID`, `订单编号`, `订单状态`, `订单时间`, `订单类型`,
        `订单备注`, `门店编号`,
        ROW_NUMBER() OVER (PARTITION BY `订单ID` ORDER BY `订单时间` DESC) AS rn
    FROM report_order
),
report_order_details AS (
    SELECT
        id AS `详单ID`, order_id AS `订单ID`, product_info AS `存货名称`,
        product_specification AS `存货规格`, product_id AS `产品ID`,
        sku_code AS `存货编码`, quantity AS `数量`, actual_amount AS `实际金额`
    FROM dwd_rps_tll_order_details_di
),
summary_table AS (
    SELECT
        uo.`门店编号`, rod.`存货名称`, rod.`实际金额`, rod.`数量`, uo.`订单时间`
    FROM unique_orders uo
    INNER JOIN report_order_details rod ON uo.`订单ID` = rod.`订单ID`
    WHERE CAST(rod.`存货编码` AS CHAR) LIKE '108%'
      AND uo.`订单状态` >= 3 AND uo.`订单状态` != 5 AND uo.rn = 1
      AND uo.`订单时间` >= '2025-01-01' AND uo.`订单时间` < '2026-01-01'
)
SELECT
    `门店编号`, `存货名称`,
    DATE_FORMAT(`订单时间`, '%Y%m') AS `月份`,
    SUM(`数量`) AS `周边报货数量`,
    SUM(`实际金额`) AS `周边报货金额`
FROM summary_table
GROUP BY `门店编号`, `存货名称`, DATE_FORMAT(`订单时间`, '%Y%m')
ORDER BY `门店编号`, `存货名称`, `月份`;

SELECT
    shop_id AS 门店编号,
    LEFT(pt, 6) AS 月份,
    SUM(amount) AS 门店流水,
    SUM(income) AS 门店实收,
    SUM(order_cnt) AS 门店订单数,
    -- 严格判断：只有当日流水大于 0 时，才计入营业天数
    COUNT(DISTINCT CASE WHEN amount > 0 THEN pt END) AS 门店营业天数
FROM ads_dbs_trade_shop_pay_channel_di
WHERE pt >= '20250101' AND pt < '20260101'
  AND (shop_id LIKE 'TLL%' OR shop_id LIKE 'ZYD%')
GROUP BY shop_id, LEFT(pt, 6)
ORDER BY 门店编号, 月份;