spider_work/deca_spider/stats.sql

-- ============================================================
-- 得卡 DECA 统计 SQL（面向领导三类需求：新增 / 商家账号 / 商品售卖进度）
-- 说明：
--   * 主表 gmt_create_time = 首次入库时间(upsert 不更新它)，故可代表「新发现」的日期。
--   * 售卖进度趋势取自每日快照表 deca_product_daily_record（按 snapshot_date）。
--   * 用到窗口函数 LAG，需 MySQL 8.0+。
-- ============================================================

-- ========== 一、新增 ==========

-- 1.1 每日新增商家数（按首次入库日）
SELECT DATE(gmt_create_time) AS dt, COUNT(*) AS new_shops
FROM deca_shop_record
GROUP BY DATE(gmt_create_time)
ORDER BY dt DESC;

-- 1.2 每日新增商品数（按首次入库日）
SELECT DATE(gmt_create_time) AS dt, COUNT(*) AS new_products
FROM deca_product_record
GROUP BY DATE(gmt_create_time)
ORDER BY dt DESC;

-- 1.3 今日新增（商家 + 商品）汇总
SELECT
  (SELECT COUNT(*) FROM deca_shop_record    WHERE DATE(gmt_create_time) = CURDATE()) AS today_new_shops,
  (SELECT COUNT(*) FROM deca_product_record WHERE DATE(gmt_create_time) = CURDATE()) AS today_new_products;


-- ========== 二、商家账号 ==========

-- 2.1 商家账号总览（按在售团购数倒序）
SELECT merchant_user_id, merchant_name, fans_count, active_groupbuy_count,
       gmt_create_time AS first_seen, gmt_modified_time AS last_seen
FROM deca_shop_record
ORDER BY active_groupbuy_count DESC, fans_count DESC;

-- 2.2 商家维度的在售商品与售卖情况（关联商品表实时汇总）
SELECT s.merchant_user_id, s.merchant_name, s.fans_count,
       COUNT(p.product_code)                         AS product_cnt,
       COALESCE(SUM(p.sold_count), 0)                AS total_sold,
       COALESCE(SUM(p.card_count), 0)                AS total_card,
       ROUND(SUM(p.sold_count) / NULLIF(SUM(p.card_count), 0) * 100, 2) AS sold_pct
FROM deca_shop_record s
LEFT JOIN deca_product_record p ON p.merchant_user_id = s.merchant_user_id
GROUP BY s.merchant_user_id, s.merchant_name, s.fans_count
ORDER BY total_sold DESC;


-- ========== 三、商品售卖进度 ==========

-- 3.1 全部商品当前售卖进度（已售/总数/剩余/进度百分比）
SELECT product_code, merchant_name, title,
       sold_count, card_count, available_stock,
       ROUND(sold_count / NULLIF(card_count, 0) * 100, 2) AS sold_pct,
       groupbuy_status_name, unit_price, gmt_modified_time AS updated_at
FROM deca_product_record
ORDER BY sold_pct DESC;

-- 3.2 单个商品的每日售卖进度趋势（把 :code 换成具体 product_code）
SELECT snapshot_date, sold_count, available_stock, card_count,
       ROUND(sold_count / NULLIF(card_count, 0) * 100, 2) AS sold_pct
FROM deca_product_daily_record
WHERE product_code = :code
ORDER BY snapshot_date;

-- 3.3 每个商品「每日新卖出」增量（今日累计已售 - 昨日累计已售）
SELECT product_code, snapshot_date, sold_count,
       sold_count - LAG(sold_count) OVER (PARTITION BY product_code ORDER BY snapshot_date) AS daily_sold
FROM deca_product_daily_record
ORDER BY product_code, snapshot_date;

-- 3.4 全站每日售出增量汇总（当天所有商品比前一天多卖出的份数合计）
SELECT snapshot_date,
       SUM(daily_sold) AS total_daily_sold
FROM (
    SELECT product_code, snapshot_date,
           sold_count - LAG(sold_count) OVER (PARTITION BY product_code ORDER BY snapshot_date) AS daily_sold
    FROM deca_product_daily_record
) t
WHERE daily_sold IS NOT NULL
GROUP BY snapshot_date
ORDER BY snapshot_date DESC;

-- 3.5 即将售罄 / 售罄商品（剩余库存少或进度高，便于关注热销）
SELECT product_code, merchant_name, title, sold_count, card_count, available_stock,
       ROUND(sold_count / NULLIF(card_count, 0) * 100, 2) AS sold_pct, groupbuy_status_name
FROM deca_product_record
WHERE card_count > 0
ORDER BY sold_pct DESC, available_stock ASC
LIMIT 50;