WITH t1 AS ( SELECT a.product_id, a.cnt, b.uid, DATE(b.event_time) AS dt FROM tb_order_detail a LEFT JOIN tb_order_overall b ON a.order_id = b.order_id LEFT JOIN tb_product_info c ON a.product_id = c.product_id WHERE b.status = 1 AND c.tag = '零食' ), t2 AS ( -- 时间维度表 SELECT max(dt) AS max_dt, DATE_AD...

WITH t_1 AS ( SELECT a.product_id, a.price, a.cnt, c.shop_id, c.in_price FROM tb_order_detail a INNER JOIN tb_order_overall b ON a.order_id = b.order_id INNER JOIN tb_product_info c ON a.product_id = c.product_id WHERE b.event_time>='2021-10-01' AND c.shop_id = 901 AND b.status != 2 ), t_2 AS ( S...

WITH t1 AS ( SELECT product_id, count(product_id) AS show_num, sum(if_click) AS click_num, sum(if_cart) AS cart_num, sum(if_payment) AS pay_num, sum(if_refund) AS refund_num FROM tb_user_event WHERE SUBSTR(event_time,1,7) = '2021-10' GROUP BY product_id ) SELECT product_id, ROUND(click_num / show_nu...

WITH t1 AS ( SELECT SUBSTR(event_time,1,7) AS month, total_amount FROM tb_order_overall WHERE status != 2 AND substr(event_time,1,4) = 2021 ) SELECT month, ROUND(sum(total_amount),0) AS GMV FROM t1 GROUP BY month HAVING GMV > 100000 ORDER BY GMV asc; # 这道题关键在于substr，或者用date_format(dt,%Y%m)，date函数...

WITH t1 AS( -- t1表筛选出活动期间内的数据，并且为了防止一天有多次签到活动，distinct 去重 SELECT DISTINCT uid, DATE(in_time) dt, DENSE_RANK() over(PARTITION BY uid ORDER BY DATE(in_time)) rn -- 编号 FROM tb_user_log WHERE DATE(in_time) BETWEEN '2021-07-07' AND '2021-10-31' AND artical_id = 0 AND sign_in = 1 ), t2 AS ( # t2 就是求出 SELE...

WITH t1 AS ( SELECT uid, in_time FROM tb_user_log UNION ALL SELECT uid, out_time AS in_time FROM tb_user_log ), t2 AS ( SELECT uid, in_time, RANK() over (PARTITION BY uid ORDER BY uid ASC, in_time ASC ) AS num_1 FROM t1 ), t3 AS ( SELECT uid, DATE (in_time) AS dt FROM t2 WHERE num_1 = 1 ), t4 AS ( #...

WITH t1 AS ( SELECT uid, in_time FROM tb_user_log UNION ALL SELECT uid, out_time AS in_time FROM tb_user_log ), t2 AS ( SELECT uid, in_time, RANK() over (PARTITION BY uid ORDER BY uid ASC, in_time ASC ) AS num_1 FROM t1 ), t3 AS ( # 获取最大时间 SELECT max(DATE(in_time)) AS max_date FROM t2 ), t4 AS ( # 获...

WITH t1 AS ( SELECT uid, in_time FROM tb_user_log UNION ALL SELECT uid, out_time AS in_time FROM tb_user_log ), t2 AS ( SELECT uid, in_time, RANK() over (PARTITION BY uid ORDER BY uid ASC, in_time ASC ) AS num_1 FROM t1 ), t3 AS ( SELECT uid, DATE (in_time) AS dt, DATE_ADD(DATE(in_time),INTERVAL 1 D...

WITH t1 AS ( SELECT * FROM tb_user_log WHERE artical_id != 0 ), t2 AS ( SELECT uid, artical_id, in_time, 1 AS uv FROM t1 UNION ALL SELECT uid, artical_id, out_time, -1 AS uv FROM t1 ), t3 AS ( SELECT artical_id, in_time, uv, sum(uv) OVER (PARTITION BY artical_id ORDER BY in_time ASC,uv DESC ) AS sum...

WITH t1 AS ( SELECT DATE(in_time) AS dt, SUM(TIMESTAMPDIFF(SECOND, in_time, out_time)) AS sum_viiew_len_sec, COUNT(DISTINCT uid) AS total FROM tb_user_log WHERE DATE (in_time) BETWEEN '2021-11-01' AND '2021-11-30' AND artical_id != 0 GROUP BY dt ) SELECT dt, ROUND(sum_viiew_len_sec / total,1) AS avg...

WITH t1 AS ( SELECT DATE(MAX(end_time)) AS max_date, DATE_ADD(DATE(MAX(end_time)), INTERVAL -29 DAY) AS min_date FROM tb_user_video_log ), t2 AS ( SELECT tu.video_id, tu.start_time, tu.end_time, tu.if_like, tu.comment_id, tu.if_retweet, if(TIMESTAMPDIFF(SECOND, start_time, end_time) / tv.duration &g...

WITH t1 AS ( SELECT *, DATE (start_time) AS dt FROM tb_user_video_log WHERE DATE (start_time) BETWEEN '2021-09-25' AND '2021-10-03' ), t2 AS ( SELECT tv.tag, t1.dt, t1.if_like, t1.if_retweet FROM t1 LEFT JOIN tb_video_info tv on t1.video_id = tv.video_id ), t3 AS ( SELECT tag, dt, SUM(if_like) AS su...

WITH t1 AS ( SELECT tv.author, SUBSTR(start_time,1,7) AS month, CASE WHEN if_follow = 0 then 0 WHEN if_follow = 1 then 1 WHEN if_follow = 2 then -1 END AS fens, COUNT(author) OVER (PARTITION BY author,SUBSTR(start_time,1,7)) AS bofangliang FROM tb_user_video_log tu LEFT JOIN tb_video_info tv ON tu.v...

WITH t1 AS ( SELECT DATE(MAX(start_time)) AS max_date, DATE_ADD(DATE(MAX(start_time)), INTERVAL -29 DAY) AS min_date FROM tb_user_video_log ), t2 AS ( SELECT tu.*, tv.tag, t1.* FROM tb_user_video_log tu JOIN t1 LEFT JOIN tb_video_info tv ON tu.video_id = tv.video_id WHERE DATE(start_time) BETWEEN mi...