【mysql】如何求滞销天数?

如何求滞销天数?

1.基础表

-- kwan.test_table definition

CREATE TABLE `test_table` (
  `id` int DEFAULT NULL COMMENT '日期',
  `flag` int DEFAULT NULL COMMENT '销量'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COMMENT='店铺信息表';

INSERT INTO kwan.test_table (id, flag) VALUES(1, 1);
INSERT INTO kwan.test_table (id, flag) VALUES(2, 0);
INSERT INTO kwan.test_table (id, flag) VALUES(3, 0);
INSERT INTO kwan.test_table (id, flag) VALUES(4, 1);
INSERT INTO kwan.test_table (id, flag) VALUES(5, 0);
INSERT INTO kwan.test_table (id, flag) VALUES(6, 0);
INSERT INTO kwan.test_table (id, flag) VALUES(7, 0);
INSERT INTO kwan.test_table (id, flag) VALUES(8, 1);
INSERT INTO kwan.test_table (id, flag) VALUES(9, 1);

2.解法

解法1

SELECT id                    AS id
     , flag                  AS flag
     , flag1                 AS flag1
     , flag2                 AS flag2
     , IF(ZX > 99999, 0, ZX) AS ZX
FROM (
  SELECT id                                                                                                AS id
  , flag                                                                                              AS flag
  , flag1                                                                                             AS flag1
  , flag2                                                                                             AS flag2
  , SUM(flag1)
  OVER (PARTITION BY flag2, flag1 ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ZX -- flag1的累加值即为连续值,因为分组
  FROM (
    SELECT id                                                                          AS id
    , flag                                                                        AS flag
    , flag1                                                                       AS flag1
    , MAX(flag1)
    OVER (ORDER BY id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS flag2 -- 到当前行的最大值
    FROM (
      SELECT id                                                       AS id
      , flag                                                     AS flag
      , IF(flag > 0, 99999 + ROW_NUMBER() OVER (ORDER BY id), 1) AS flag1 -- 无售卖就为1,有售卖设置一个特别大的值
      FROM kwan.test_table
    ) t
    ORDER BY id ASC
  ) t
) t
ORDER BY id;

解法2:

SELECT *
FROM (
         SELECT n1.id, n1.flag
         FROM (
                  SELECT id, (CASE flag WHEN 1 THEN 0 ELSE 1 END) AS flag
                  FROM test_table
                  WHERE flag = 1) n1
         UNION ALL
         SELECT n2.id, n2.flag
         FROM (
                  SELECT m2.id
                       , m2.flag
                       , row_number() over(partition BY m2.id ORDER BY m2.flag ASC)  AS order_no
                  FROM (
                           SELECT t1.id, (t1.order_no - t2.order_no) AS flag
                           FROM (
                                    SELECT id
                                         , (CASE flag WHEN 1 THEN 0 ELSE 1 END) AS flag
                                         , row_number()                            over(ORDER BY id)  AS order_no
                                    FROM test_table
                                ) t1
                                    LEFT JOIN
                                (
                                    SELECT id
                                         , flag
                                         , row_number() over( ORDER BY id)  AS order_no
                                    FROM test_table
                                ) t2 ON t1.flag = t2.flag
                           WHERE t1.flag = 1
                             AND t1.order_no > t2.order_no
                       ) m2
              ) n2
         WHERE n2.order_no = 1
    order by id
     ) n
ORDER BY n.id

解法3:最佳

SELECT t2.product_key      AS product_key      --sku
     , t2.gender_name      AS gender_name      --性别
     , t2.category_name1   AS category_name1   --类别
     , t2.brand_detail_no  AS brand_detail_no  --品牌
     , t2.period_sdate     AS period_sdate     --日期
     , t2.managing_city_no AS managing_city_no --城市
     , t2.store_key        AS store_key        --店铺
     , t2.un_sal_flag      AS un_sal_flag      --销售标识
     , t2.rn - t2.rn_flag  AS un_sal_day       --滞销天数
FROM (
  SELECT t1.product_key      AS product_key      --sku
  , t1.gender_name      AS gender_name      --性别
  , t1.category_name1   AS category_name1   --类别
  , t1.brand_detail_no  AS brand_detail_no  --品牌
  , t1.period_sdate     AS period_sdate     --日期
  , t1.managing_city_no AS managing_city_no --城市
  , t1.store_key        AS store_key        --店铺
  , t1.un_sal_flag      AS un_sal_flag      --售卖标识
  , t1.rn               AS rn               --行序号
  , MAX(t1.rn_flag)        OVER (PARTITION BY t1.product_key,t1.store_key ORDER BY t1.period_sdate) AS rn_flag --当前行有售卖的最大记录行号
  FROM (
    SELECT product_key                                                                                           AS product_key      --sku
    , gender_name                                                                                           AS gender_name      --性别
    , category_name1                                                                                        AS category_name1   --类别
    , brand_detail_no                                                                                       AS brand_detail_no  --品牌
    , period_sdate                                                                                          AS period_sdate     --日期
    , managing_city_no                                                                                      AS managing_city_no --城市
    , store_key                                                                                             AS store_key        --店铺
    , un_sal_flag                                                                                           AS un_sal_flag      --销售标识
    , IF(un_sal_flag > 0, ROW_NUMBER() OVER (PARTITION BY product_key,store_key ORDER BY period_sdate ), 0) AS rn_flag          -- 对有销售的排序,无销售的为0
    , ROW_NUMBER()                                                                                             OVER (PARTITION BY product_key,store_key ORDER BY period_sdate )                  AS rn
    FROM ads_sense_rep.tmp_common_category_overview_17
  ) t1
) t2 distribute BY period_sdate,CAST(rand()*50 AS INT)
;