赠茶数据查询#

品牌管理部赠茶报表使用的查询：指定时段、指定城市内，实收为 0 的茶品销量明细。

数据源表：ads_dbs_trade_food_di
指标：销量 / 流水 / 实收
口径：实收金额为 0 的茶视为赠茶

SQL#

WITH day_periods AS (
    SELECT '20260327' AS start_date, '20260504' AS end_date
)
SELECT
    CONCAT(day_periods.start_date, '~', day_periods.end_date) AS 时段,
    business_date AS 日期,
    city_name AS 城市,
    item_name AS 产品名称,
    SUM(dp_item_count) AS 销量,
    SUM(dp_total_amount) AS 流水,
    SUM(dp_pay_amount) AS 实收
FROM
    ads_dbs_trade_food_di
CROSS JOIN day_periods
WHERE
    business_date BETWEEN day_periods.start_date AND day_periods.end_date
    AND city_name IN ('蚌埠市','阜阳市','淮南市')
GROUP BY
    时段,
    日期,
    城市,
    产品名称
HAVING
    SUM(dp_item_count) > 0
    AND SUM(dp_pay_amount) = 0;

参数调整#

修改 day_periods 中的起止日期切换时段
修改 city_name IN (...) 城市清单

注意事项#

注意

导出后筛选 青韵乌龙、茉莉初露、每日鲜泡好茶 相关产品；因可能新增不规范名称，不在 SQL 阶段过滤。