周边数据需求#

周边数据分析
├─ main.py
├─ query1.csv
├─ query2.csv
├─ query3.csv
├─ ReadMe.md
└─ 周边数据需求.db

SELECT 
    stat_shop_id AS 门店编号, 
    item_name AS 周边产品名称, 
    SUM(dp_item_count) AS 周边产品销量, 
    SUM(dp_total_amount) AS 周边产品流水 
FROM 
    ads_dbs_trade_food_di 
WHERE 
    food_category_name in ('周边产品' ,'周边')
    AND business_date like '202604%'
GROUP BY 
    stat_shop_id,item_name 
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.`数量` 
    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.`订单时间` >= '2026-04-01' AND uo.`订单时间` < '2026-05-01'
)
SELECT 
    `门店编号`, 
    `存货名称`, 
    SUM(`数量`) AS `周边报货数量`, 
    SUM(`实际金额`) AS `周边报货金额` 
FROM summary_table 
GROUP BY `门店编号`, `存货名称` 
ORDER BY `门店编号`, `存货名称`;

SELECT 
    shop_id AS 门店编号, 
    SUM(amount) AS 门店流水, 
    SUM(income) AS 门店实收, 
    SUM(order_cnt) AS 门店订单数 
FROM ads_dbs_trade_shop_pay_channel_di 
WHERE pt like '202604%'
  AND (shop_id LIKE 'TLL%' OR shop_id LIKE 'ZYD%') 
GROUP BY shop_id 
ORDER BY 门店编号;