周边数据需求#
需求背景#
背景
零食事业部需要的周边相关报表。
需求频率#
频率
每月第一个工作日，发送至：
- 零食事业部 @李慧 @刘垚
- 市场运营中心 @单磊
目录结构#
周边数据分析
├─ main.py
├─ 周边产品销售查询.csv
├─ 周边产品报货查询.csv
├─ 门店流水查询.csv
├─ ReadMe.md
└─ 周边数据需求.db数据查询及导出#
查询周边销售数据#
查询结果保存为 周边产品销售查询.csv。
注意
营业天数：只有当日销量大于 0 时，才计入营业天数。
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;查询周边报货数据#
查询结果保存为 周边产品报货查询.csv。
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 `门店编号`, `存货名称`, `月份`;查询门店销售数据#
查询结果保存为 门店流水查询.csv。
注意
营业天数：只有当日流水大于 0 时，才计入营业天数。
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 门店编号, 月份;