spider_work/cxx_spider/stats/super_vault_stats.sql

-- ============================================================
-- 超级仓库 SuperVault · 已售统计 SQL（自营·全站累计口径）
-- 日期：2026/08/14
-- 数据源：super_vault_product_record / super_vault_report_record
-- 统计口径：
--   0) 统计范围 = sold_count>0 且 status NOT IN (0,1,2,3)（剔除未开卖与未开始/预售/进行中等未成交态，
--      只留 status 4/6/7/8/9/10/12 等有效成交态）。
--      —— 平台为自营，全站视为一个商家；用「主播 anchor」作二级拆分维度。
--      —— 主播按 anchor_username 分组：同名不同 anchor_id（如两个「北京小周」）合并为一位主播。
--   1) 销售额 = SUM(sign_price * count)  —— 单价×总份数=满仓总价（随机卡种/未售罄团会高于实收 total_price）
--   2) 成团数 = 拼团商品数（sold_count>0 且 status NOT IN(0,1,2,3)）
--   3) 参与人数 = 拆卡报告 user_name 去重（中卡用户近似，仅覆盖有报告的商品）
--   4) 均拼单价 = 销售额 / 成团数
--   5) 人均消费 = 销售额 / 参与人数
--   6) 成交时间 = COALESCE(completion_time, sold_time, sell_time)：完成→售罄成交→开售 依次兜底
--   —— 若只统计某段时间：给主查询与子查询都加成交锚点过滤，例如
--      AND COALESCE(p.completion_time, p.sold_time, p.sell_time)
--          BETWEEN '2026-08-01 00:00:00' AND '2026-09-01 00:00:00'
--      （两段 SELECT 各自独立，可在 Navicat 单独选中执行）
-- ============================================================


-- ------------------------------------------------------------
-- 一、平台大盘（自营·全站累计）
-- ------------------------------------------------------------
SELECT
    -- 销售额（元）= 单价×总份数
    ROUND(SUM(p.sign_price * p.count), 2)                                        AS 销售额,

    -- 主播数（按主播名去重，合并同名）
    COUNT(DISTINCT p.anchor_username)                                            AS 主播数,

    -- 成团数
    COUNT(*)                                                                     AS 成团数,

    -- 均拼单价（销售额 / 成团数）
    ROUND(SUM(p.sign_price * p.count) / NULLIF(COUNT(*), 0), 2)                  AS 均拼单价,

    -- 参与人数（拆卡报告中卡用户昵称去重）
    (SELECT COUNT(DISTINCT rr.user_name)
       FROM super_vault_report_record rr
       JOIN super_vault_product_record pp ON pp.pid = rr.pid
      WHERE pp.sold_count > 0 AND pp.status NOT IN (0, 1, 2, 3)
        AND rr.user_name IS NOT NULL AND rr.user_name <> '')                     AS 参与人数,

    -- 人均消费（销售额 / 参与人数）
    ROUND(
        SUM(p.sign_price * p.count) /
        NULLIF((SELECT COUNT(DISTINCT rr.user_name)
                  FROM super_vault_report_record rr
                  JOIN super_vault_product_record pp ON pp.pid = rr.pid
                 WHERE pp.sold_count > 0 AND pp.status NOT IN (0, 1, 2, 3)
                   AND rr.user_name IS NOT NULL AND rr.user_name <> ''), 0),
        2
    )                                                                            AS 人均消费
FROM super_vault_product_record p
WHERE p.sold_count > 0 AND p.status NOT IN (0, 1, 2, 3);


-- ------------------------------------------------------------
-- 二、各主播汇总（按主播名分组·合并同名，按销售额倒序）
-- ------------------------------------------------------------
SELECT
    p.anchor_username                                                            AS 主播名,

    -- 销售额 = 单价×总份数
    ROUND(SUM(p.sign_price * p.count), 2)                                        AS 销售额,

    -- 成团数
    COUNT(*)                                                                     AS 成团数,

    -- 参与人数（该主播商品拆卡报告中卡用户去重）
    (SELECT COUNT(DISTINCT rr.user_name)
       FROM super_vault_report_record rr
       JOIN super_vault_product_record pp ON pp.pid = rr.pid
      WHERE pp.anchor_username = p.anchor_username
        AND pp.sold_count > 0 AND pp.status NOT IN (0, 1, 2, 3)
        AND rr.user_name IS NOT NULL AND rr.user_name <> '')                    AS 参与人数,

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

    -- 人均消费
    ROUND(
        SUM(p.sign_price * p.count) /
        NULLIF((SELECT COUNT(DISTINCT rr.user_name)
                  FROM super_vault_report_record rr
                  JOIN super_vault_product_record pp ON pp.pid = rr.pid
                 WHERE pp.anchor_username = p.anchor_username
                   AND pp.sold_count > 0 AND pp.status NOT IN (0, 1, 2, 3)
                   AND rr.user_name IS NOT NULL AND rr.user_name <> ''), 0),
        2
    )                                                                            AS 人均消费
FROM super_vault_product_record p
WHERE p.sold_count > 0 AND p.status NOT IN (0, 1, 2, 3)
GROUP BY p.anchor_username
ORDER BY 销售额 DESC;


-- ------------------------------------------------------------
-- 三、单个主播每条组队明细（把主播名换成具体主播，如 '郑宇航'，按总金额倒序）
--   参与人数(本团) = 该团拆卡报告 user_name 去重
-- ------------------------------------------------------------
SELECT
    p.title                                                                     AS 团名,
    p.serial                                                                     AS 编号,
    p.type_name                                                                 AS 卡种,
    p.standard_name                                                             AS 规格,
    p.sign_price                                                                AS 单价,
    p.count                                                                     AS 总份数,
    ROUND(p.sold_count / NULLIF(p.count, 0) * 100, 1)                           AS 进度百分比,
    ROUND(p.sign_price * p.count, 2)                                            AS 总金额,
    (SELECT COUNT(DISTINCT rr.user_name) FROM super_vault_report_record rr
      WHERE rr.pid = p.pid AND rr.user_name IS NOT NULL AND rr.user_name <> '') AS 参与人数,
    p.sell_time                                                                 AS 开售时间,
    -- 成交时间 = 完成时间(发货完成态才有) → 售罄成交时刻 sold_time → 依次兜底
    COALESCE(p.completion_time, p.sold_time)                                    AS 成交时间,
    TIMESTAMPDIFF(SECOND, p.sell_time, COALESCE(p.completion_time, p.sold_time)) AS 售卖时长秒
FROM super_vault_product_record p
WHERE p.sold_count > 0 AND p.status NOT IN (0, 1, 2, 3)
  AND p.anchor_username = '郑宇航'   -- ← 改这里选主播
ORDER BY 总金额 DESC;