spider_work/deca_spider/stats/stats_sold.sql

-- ============================================================
-- 得卡 DECA · 已售流程统计 SQL（按 completed_at 统计「昨天17点~今天6点」成交）
-- 日期：2026/08/05
-- 数据源：deca_product_record / deca_report_record
-- 统计口径：
--   0) 统计范围 = 成交完成时间 completed_at 落在 [昨天 17:00:00, 今天 06:00:00] 的已售商品
--      —— completed_at 为 varchar 存 'YYYY-MM-DD HH:MM:SS'，与 datetime 边界直接比较即可
--      —— 起点 (CURDATE() - INTERVAL 1 DAY) + INTERVAL 17 HOUR = 昨天17:00
--         终点 CURDATE() + INTERVAL 6 HOUR = 今天06:00（含两端）
--      —— 表达式直接内联在每段查询，两段 SELECT 各自独立、可在 Navicat 单独选中执行
--   1) 销售额 = SUM(sold_count * unit_price)（每商品 已售份数 × 单价 之和）
--   2) 成团数 = 该时段成交的已售商品数（已售列表返回的都是卖过的，默认都算成团）
--   3) 参与人数 = 该时段成交商品对应拆卡报告里的中卡用户昵称去重
--      —— 已售接口不返回 buyer 明细，只能用拆卡报告近似，仅覆盖 report_state=1 的商品
--   4) 均拼单价 = 销售额 / 成团数（单笔团购平均总金额，非 unit_price）
--   5) 人均消费 = 销售额 / 参与人数
-- 用法：每天跑一次即可（默认取昨天17点~今天6点）。要跑指定日期区间，把两处边界表达式
--       换成 '2026-08-04 17:00:00' / '2026-08-05 06:00:00' 这样的字面量即可。
-- ============================================================


-- ------------------------------------------------------------
-- 一、平台大盘（昨天成交口径，对应图片 6 个卡片）
--   参与人数用 deca_report_record.hit_user_nickname 去重
--   —— 只覆盖昨天成交且 report_state=1（已采到报告）的商品
-- ------------------------------------------------------------
SELECT
    -- 销售额（元）
    ROUND(SUM(p.sold_count * p.unit_price), 2)                       AS 销售额,

    -- 商家数（昨天有成交商品的去重商家）
    COUNT(DISTINCT p.merchant_user_id)                               AS 商家数,

    -- 成团数（昨天成交的已售商品数）
    COUNT(*)                                                          AS 成团,

    -- 均拼单价（销售额 / 成团数，单笔团购平均总金额）
    ROUND(SUM(p.sold_count * p.unit_price) / NULLIF(COUNT(*), 0), 2) AS 均拼单价,

    -- 参与人数（昨天成交商品的拆卡报告中卡用户昵称去重）
    (SELECT COUNT(DISTINCT r.hit_user_nickname)
       FROM deca_report_record r
       JOIN deca_product_record pp ON pp.product_code = r.product_code
      WHERE pp.completed_at >= (CURDATE() - INTERVAL 1 DAY) + INTERVAL 17 HOUR AND pp.completed_at <= CURDATE() + INTERVAL 6 HOUR
        AND r.hit_user_nickname IS NOT NULL
        AND r.hit_user_nickname <> '')                                AS 参与人数,

    -- 人均消费（销售额 / 参与人数）
    ROUND(
        SUM(p.sold_count * p.unit_price) /
        NULLIF((SELECT COUNT(DISTINCT r.hit_user_nickname)
                  FROM deca_report_record r
                  JOIN deca_product_record pp ON pp.product_code = r.product_code
                 WHERE pp.completed_at >= (CURDATE() - INTERVAL 1 DAY) + INTERVAL 17 HOUR AND pp.completed_at <= CURDATE() + INTERVAL 6 HOUR
                   AND r.hit_user_nickname IS NOT NULL
                   AND r.hit_user_nickname <> ''), 0),
        2
    )                                                                 AS 人均消费
FROM deca_product_record p
WHERE p.completed_at >= (CURDATE() - INTERVAL 1 DAY) + INTERVAL 17 HOUR
  AND p.completed_at <= CURDATE() + INTERVAL 6 HOUR
  AND p.unit_price IS NOT NULL
  AND p.sold_count IS NOT NULL;


-- ------------------------------------------------------------
-- 二、指定商家（881226408）昨天成交：销售额/成团数/参与人数
-- ------------------------------------------------------------
SELECT
    MAX(p.merchant_name)                                                  AS 商家名,

    -- 销售额
    ROUND(SUM(p.sold_count * p.unit_price), 2)                            AS 销售额,

    -- 成团数（昨天成交）
    COUNT(*)                                                              AS 成团数,

    -- 参与人数（该商家昨天成交且已采报告商品的中卡用户昵称去重）
    (SELECT COUNT(DISTINCT r.hit_user_nickname)
       FROM deca_report_record r
       JOIN deca_product_record pp ON pp.product_code = r.product_code
      WHERE pp.merchant_user_id = '881226408'
        AND pp.completed_at >= (CURDATE() - INTERVAL 1 DAY) + INTERVAL 17 HOUR AND pp.completed_at <= CURDATE() + INTERVAL 6 HOUR
        AND r.hit_user_nickname IS NOT NULL
        AND r.hit_user_nickname <> '')                                    AS 参与人数,

    -- 均拼单价
    ROUND(SUM(p.sold_count * p.unit_price) / NULLIF(COUNT(*), 0), 2)      AS 均拼单价,

    -- 人均消费（销售额 / 参与人数）
    ROUND(
        SUM(p.sold_count * p.unit_price) /
        NULLIF((SELECT COUNT(DISTINCT r.hit_user_nickname)
                  FROM deca_report_record r
                  JOIN deca_product_record pp ON pp.product_code = r.product_code
                 WHERE pp.merchant_user_id = '881226408'
                   AND pp.completed_at >= (CURDATE() - INTERVAL 1 DAY) + INTERVAL 17 HOUR AND pp.completed_at <= CURDATE() + INTERVAL 6 HOUR
                   AND r.hit_user_nickname IS NOT NULL
                   AND r.hit_user_nickname <> ''), 0),
        2
    )                                                                     AS 人均消费

FROM deca_product_record p
WHERE p.merchant_user_id = '881226408'
  AND p.completed_at >= (CURDATE() - INTERVAL 1 DAY) + INTERVAL 17 HOUR
  AND p.completed_at <= CURDATE() + INTERVAL 6 HOUR
  AND p.unit_price IS NOT NULL
  AND p.sold_count IS NOT NULL
GROUP BY p.merchant_user_id;