SQL練習(xí)題三十-每季十題(九)

340. 用戶連續(xù)消費的月份【小紅書一面】

有表log_consume
uid,order_time,order_price

A,2022-01-01,100
A,2022-01-02,100
A,2022-02-02,100
A,2022-03-02,100
A,2022-04-02,100
A,2022-05-02,100
B,2021-01-02,100
B,2021-01-02,100
B,2021-03-02,100
B,2021-05-02,100
B,2021-07-02,100
B,2021-11-02,100
B,2021-12-02,100
B,2022-01-02,100
B,2022-02-02,100

create table log_consume(
    uid  varchar(10),
    order_time date,
    order_price double
);


insert into log_consume value('A','2022-01-01',100);
insert into log_consume value('A','2022-01-02',100);
insert into log_consume value('A','2022-02-02',100);
insert into log_consume value('A','2022-03-02',100);
insert into log_consume value('A','2022-04-02',100);
insert into log_consume value('A','2022-05-02',100);
insert into log_consume value('B','2021-01-02',100);
insert into log_consume value('B','2021-01-02',100);
insert into log_consume value('B','2021-03-02',100);
insert into log_consume value('B','2021-05-02',100);
insert into log_consume value('B','2021-07-02',100);
insert into log_consume value('B','2021-11-02',100);
insert into log_consume value('B','2021-12-02',100);
insert into log_consume value('B','2022-01-02',100);
insert into log_consume value('B','2022-02-02',100);

也就是連續(xù)登陸案例的變形題

  1. 求用戶連續(xù)消費的月份

我這mysql開窗出bug了,先這樣寫吧

with x as (
                         select uid,
                                order_month,
                                 IF(month_sum = 1, 0, 1)  as tag
                         from (
                                  SELECT uid,
                                         order_month,
                                         lag_order_month,
                                         ((year(order_month) - year(lag_order_month)) * 12 + month(order_month) -
                                          month(lag_order_month)) as month_sum
                                  FROM (
                                           select uid,
                                                  order_month,
                                                  lag(order_month, 1, order_month)
                                                      over (partition by uid order by order_month asc ) as lag_order_month
                                           from (
                                                    SELECT uid, concat(SUBSTR(order_time, 1, 7), '-01') AS order_month
                                                    FROM log_consume
                                                    GROUP BY uid, concat(SUBSTR(order_time, 1, 7), '-01')
                                                ) t1
                                       ) t2
                              ) t3 )
select
uid,k,group_concat(order_month)
from (
select
uid,
order_month,tag,sum(tag) over (), sum(tag) over (partition by uid),sum(tag) over (partition by uid order by order_month) as k
from x ) t1 group by uid,k having count(1) >= 2;
問題的解決

為什么會出現(xiàn)這種結(jié)果,我很詫異.....

select uid, order_month, tag,sum(tag) over (partition by uid order by order_month rows between unbounded preceding and current row)
from (
                         select uid,
                                order_month,
                                 IF(month_sum = 1, 0, 1)  as tag
                         from (
                                  SELECT uid,
                                         order_month,
                                         lag_order_month,
                                         ((year(order_month) - year(lag_order_month)) * 12 + month(order_month) -
                                          month(lag_order_month)) as month_sum
                                  FROM (
                                           select uid,
                                                  order_month,
                                                  lag(order_month, 1, order_month)
                                                      over (partition by uid order by order_month asc ) as lag_order_month
                                           from (
                                                    SELECT uid, concat(SUBSTR(order_time, 1, 7), '-01') AS order_month
                                                    FROM log_consume
                                                    GROUP BY uid, concat(SUBSTR(order_time, 1, 7), '-01')
                                                ) t1
                                       ) t2
                              ) t3 ) t4;

要顯式顯示窗口的大小規(guī)則.......這樣就沒問題

341. 用戶連續(xù)消費兩個月岛宦，但是第三個月不消費【小紅書一面】

就是求取連續(xù)消費兩個月
count(1) = 2 即可

如果跨年重新計算呢?

342. 基于掃碼記錄查找密接人員

create table log_trail(
    uid varchar(11),
    area varchar(10),
    scan_time varchar(24)

);


insert into log_trail value ('1000001','100A','2022-01-01 09:01:09');
insert into log_trail value ('1000001','100A','2022-01-01 12:01:09');
insert into log_trail value ('1000001','100A','2022-01-01 13:01:09');
insert into log_trail value ('1000001','100B','2022-01-01 14:01:09');
insert into log_trail value ('1000001','100B','2022-01-01 19:01:09');
insert into log_trail value ('1000001','100A','2022-01-01 20:01:09');
insert into log_trail value ('1000001','100A','2022-01-01 21:01:09');
insert into log_trail value ('1000001','100A','2022-01-01 23:01:09');

insert into log_trail value ('1000002','100A','2022-01-01 09:01:09');
insert into log_trail value ('1000002','100A','2022-01-01 10:01:09');
insert into log_trail value ('1000002','100A','2022-01-01 11:01:09');
  1. 找到每個用戶在每個區(qū)域的開始時間和結(jié)束時間(存在跨區(qū)的情況)
select
uid,area,tag,min(scan_time) as start_time ,max(scan_time) as end_time
from (
select uid, area, scan_time,row_number() over (partition by uid order by scan_time) - row_number() over (partition by uid,area order by scan_time) as tag
from log_trail ) t1
group by uid,area,tag
;
  1. 假如某個用戶核酸檢查結(jié)果為陽性,找出他的伴隨人員?伴隨規(guī)則:在陽性人員停留半小時以上的區(qū)域,用戶停留時間在半小時以上,并且停留時間和陽性人員有十分鐘以上的交集
    分三種情況的
with x as (
select
uid,area,tag,min(scan_time) as start_time ,max(scan_time) as end_time
from (
select uid, area, scan_time,row_number() over (partition by uid order by scan_time) - row_number() over (partition by uid,area order by scan_time) as tag
from log_trail  ) t1
group by uid,area,tag
having  min(scan_time) + interval 30  minute  <=  max(scan_time) )

select t2.uid,t2.uid, t2.area, t2.start_time, t2.end_time
from (
select uid,area,start_time,end_time
from x where uid = '1000002' ) t1
join (
   select uid,area,start_time,end_time
from x where uid <>  '1000002'
    ) t2
on t1.area = t2.area
and  t1.start_time + interval 10  minute <= t2.end_time
and  t2.start_time + interval 10  minute <= t1.end_time

343. 同比梯码、環(huán)比增長率

之前有篇博客有講SQL怎么寫
SQL-分組月度環(huán)比同比,思路很簡單就是偏移量函數(shù)結(jié)合排序的應(yīng)用,注意點就是可能某個月沒有數(shù)據(jù)會導(dǎo)致計算錯誤,需要使用full join 來避免問題的

344. 計算直播間的人氣值

直播開播記錄表
直播觀看記錄表

ACU為平均同時在線人數(shù),計算方式為:觀眾側(cè)觀看時長/某場直播的開播時長,沒有人觀看的時候顯示0

345. 地鐵換乘線路查詢☆

  1. mysql遞歸
with  recursive t(n) as (
     select  1 as n  -- 初始化
     union  all
     select  n + 1 from t where n < 10  -- 遞歸式

    )
select  * from t;

CREATE TABLE bj_subway(
  station_id INT NOT NULL PRIMARY KEY,
  line_name  VARCHAR(20) NOT NULL,
  station_name VARCHAR(50) NOT NULL,
  next_station VARCHAR(50) NOT NULL,
  direction VARCHAR(50) NOT NULL
);

-- 初始化數(shù)據(jù)筹燕，目前只有1號線、2號線和8號線的數(shù)據(jù)
INSERT INTO bj_subway VALUES (1,'1號線','蘋果園','古城','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (2,'1號線','古城','八角游樂園','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (3,'1號線','八角游樂園','八寶山','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (4,'1號線','八寶山','玉泉路','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (5,'1號線','玉泉路','五棵松','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (6,'1號線','五棵松','萬壽路','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (7,'1號線','萬壽路','公主墳','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (8,'1號線','公主墳','軍事博物館','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (9,'1號線','軍事博物館','木樨地','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (10,'1號線','木樨地','南禮士路','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (11,'1號線','南禮士路','復(fù)興門','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (12,'1號線','復(fù)興門','西單','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (13,'1號線','西單','天安門西','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (14,'1號線','天安門西','天安門東','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (15,'1號線','天安門東','王府井','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (16,'1號線','王府井','東單','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (17,'1號線','東單','建國門','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (18,'1號線','建國門','永安里','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (19,'1號線','永安里','國貿(mào)','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (20,'1號線','國貿(mào)','大望路','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (21,'1號線','大望路','四惠','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (22,'1號線','四惠','四惠東','蘋果園—四惠東');
INSERT INTO bj_subway VALUES (23,'1號線','四惠東','四惠','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (24,'1號線','四惠','大望路','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (25,'1號線','大望路','國貿(mào)','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (26,'1號線','國貿(mào)','永安里','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (27,'1號線','永安里','建國門','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (28,'1號線','建國門','東單','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (29,'1號線','東單','王府井','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (30,'1號線','王府井','天安門東','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (31,'1號線','天安門東','天安門西','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (32,'1號線','天安門西','西單','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (33,'1號線','西單','復(fù)興門','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (34,'1號線','復(fù)興門','南禮士路','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (35,'1號線','南禮士路','木樨地','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (36,'1號線','木樨地','軍事博物館','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (37,'1號線','軍事博物館','公主墳','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (38,'1號線','公主墳','萬壽路','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (39,'1號線','萬壽路','五棵松','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (40,'1號線','五棵松','玉泉路','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (41,'1號線','玉泉路','八寶山','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (42,'1號線','八寶山','八角游樂園','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (43,'1號線','八角游樂園','古城','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (44,'1號線','古城','蘋果園','四惠東—蘋果園');
INSERT INTO bj_subway VALUES (45,'2號線','西直門','積水潭','外環(huán)');
INSERT INTO bj_subway VALUES (46,'2號線','積水潭','鼓樓大街','外環(huán)');
INSERT INTO bj_subway VALUES (47,'2號線','鼓樓大街','安定門','外環(huán)');
INSERT INTO bj_subway VALUES (48,'2號線','安定門','雍和宮','外環(huán)');
INSERT INTO bj_subway VALUES (49,'2號線','雍和宮','東直門','外環(huán)');
INSERT INTO bj_subway VALUES (50,'2號線','東直門','東四十條','外環(huán)');
INSERT INTO bj_subway VALUES (51,'2號線','東四十條','朝陽門','外環(huán)');
INSERT INTO bj_subway VALUES (52,'2號線','朝陽門','建國門','外環(huán)');
INSERT INTO bj_subway VALUES (53,'2號線','建國門','北京站','外環(huán)');
INSERT INTO bj_subway VALUES (54,'2號線','北京站','崇文門','外環(huán)');
INSERT INTO bj_subway VALUES (55,'2號線','崇文門','前門','外環(huán)');
INSERT INTO bj_subway VALUES (56,'2號線','前門','和平門','外環(huán)');
INSERT INTO bj_subway VALUES (57,'2號線','和平門','宣武門','外環(huán)');
INSERT INTO bj_subway VALUES (58,'2號線','宣武門','長椿街','外環(huán)');
INSERT INTO bj_subway VALUES (59,'2號線','長椿街','復(fù)興門','外環(huán)');
INSERT INTO bj_subway VALUES (60,'2號線','復(fù)興門','阜成門','外環(huán)');
INSERT INTO bj_subway VALUES (61,'2號線','阜成門','車公莊','外環(huán)');
INSERT INTO bj_subway VALUES (62,'2號線','車公莊','西直門','外環(huán)');
INSERT INTO bj_subway VALUES (63,'2號線','車公莊','阜成門','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (64,'2號線','阜成門','復(fù)興門','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (65,'2號線','復(fù)興門','長椿街','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (66,'2號線','長椿街','宣武門','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (67,'2號線','宣武門','和平門','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (68,'2號線','和平門','前門','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (69,'2號線','前門','崇文門','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (70,'2號線','崇文門','北京站','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (71,'2號線','北京站','建國門','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (72,'2號線','建國門','朝陽門','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (73,'2號線','朝陽門','東四十條','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (74,'2號線','東四十條','東直門','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (75,'2號線','東直門','雍和宮','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (76,'2號線','雍和宮','安定門','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (77,'2號線','安定門','鼓樓大街','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (78,'2號線','鼓樓大街','積水潭','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (79,'2號線','積水潭','西直門','內(nèi)環(huán)');
INSERT INTO bj_subway VALUES (80,'2號線','西直門','車公莊','外環(huán)');
INSERT INTO bj_subway VALUES (81, '8號線', '朱辛莊', '育知路', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (82, '8號線', '育知路', '平西府', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (83, '8號線', '平西府', '回龍觀東大街', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (84, '8號線', '回龍觀東大街', '霍營', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (85, '8號線', '霍營', '育新', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (86, '8號線', '育新', '西小口', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (87, '8號線', '西小口', '永泰莊', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (88, '8號線', '永泰莊', '林萃橋', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (89, '8號線', '林萃橋', '森林公園南門', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (90, '8號線', '森林公園南門', '奧林匹克公園', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (91, '8號線', '奧林匹克公園', '奧體中心', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (92, '8號線', '奧體中心', '北土城', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (93, '8號線', '北土城', '安華橋', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (94, '8號線', '安華橋', '安德里北街', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (95, '8號線', '安德里北街', '鼓樓大街', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (96, '8號線', '鼓樓大街', '什剎海', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (97, '8號線', '什剎海', '南鑼鼓巷', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (98, '8號線', '南鑼鼓巷', '中國美術(shù)館', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (99, '8號線', '中國美術(shù)館', '金魚胡同', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (100, '8號線', '金魚胡同', '王府井', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (101, '8號線', '王府井', '前門', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (102, '8號線', '前門', '珠市口', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (103, '8號線', '珠市口', '天橋', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (104, '8號線', '天橋', '永定門外', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (105, '8號線', '永定門外', '木樨園', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (106, '8號線', '木樨園', '海戶屯', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (107, '8號線', '海戶屯', '大紅門', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (108, '8號線', '大紅門', '大紅門南', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (109, '8號線', '大紅門南', '和義', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (110, '8號線', '和義', '東高地', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (111, '8號線', '東高地', '火箭萬源', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (112, '8號線', '火箭萬源', '五福堂', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (113, '8號線', '五福堂', '德茂', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (114, '8號線', '德茂', '瀛海', '朱辛莊-瀛海');
INSERT INTO bj_subway VALUES (115, '8號線', '瀛海', '德茂', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (116, '8號線', '德茂', '五福堂', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (117, '8號線', '五福堂', '火箭萬源', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (118, '8號線', '火箭萬源', '東高地', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (119, '8號線', '東高地', '和義', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (120, '8號線', '和義', '大紅門南', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (121, '8號線', '大紅門南', '大紅門', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (122, '8號線', '大紅門', '海戶屯', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (123, '8號線', '海戶屯', '木樨園', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (124, '8號線', '木樨園', '永定門外', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (125, '8號線', '永定門外', '天橋', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (126, '8號線', '天橋', '珠市口', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (127, '8號線', '珠市口', '前門', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (128, '8號線', '前門', '王府井', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (129, '8號線', '王府井', '金魚胡同', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (130, '8號線', '金魚胡同', '中國美術(shù)館', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (131, '8號線', '中國美術(shù)館', '南鑼鼓巷', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (132, '8號線', '南鑼鼓巷', '什剎海', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (133, '8號線', '什剎海', '鼓樓大街', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (134, '8號線', '鼓樓大街', '安德里北街', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (135, '8號線', '安德里北街', '安華橋', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (136, '8號線', '安華橋', '北土城', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (137, '8號線', '北土城', '奧體中心', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (138, '8號線', '奧體中心', '奧林匹克公園', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (139, '8號線', '奧林匹克公園', '森林公園南門', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (140, '8號線', '森林公園南門', '林萃橋', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (141, '8號線', '林萃橋', '永泰莊', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (142, '8號線', '永泰莊', '西小口', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (143, '8號線', '西小口', '育新', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (144, '8號線', '育新', '霍營', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (145, '8號線', '霍營', '回龍觀東大街', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (146, '8號線', '回龍觀東大街', '平西府', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (147, '8號線', '平西府', '育知路', '瀛海-朱辛莊');
INSERT INTO bj_subway VALUES (148, '8號線', '育知路', '朱辛莊', '瀛海-朱辛莊');

WITH RECURSIVE transfer (start_station, stop_station, stops, path) AS (
  SELECT station_name, next_station, 1, CAST(CONCAT(line_name,station_name , '->', line_name,next_station) AS CHAR(1000))
    FROM bj_subway WHERE station_name = '王府井'
   UNION ALL
  SELECT p.start_station, e.next_station, stops + 1, CONCAT(p.path, '->', e.line_name, e.next_station)
    FROM transfer p
    JOIN bj_subway e
      ON p.stop_station = e.station_name AND (INSTR(p.path, e.next_station) = 0)
)
SELECT * FROM transfer WHERE stop_station ='積水潭';

346. 銀行可疑支付交易監(jiān)控

  1. 找出 5 天之內(nèi)累積轉(zhuǎn)賬超過 100 萬的賬號(限制窗口大小)
  2. 找出相同收付款人5天內(nèi)連續(xù)轉(zhuǎn)賬3次以上的記錄(限制窗口大小)

347. 微信好友關(guān)系

348. 查找優(yōu)秀員工

集合的交并運算

349. 生成日歷表

-- 創(chuàng)建日歷表
CREATE TABLE calendar(
  calendar_id    INT NOT NULL PRIMARY KEY, -- 日歷編號
  calendar_date  DATE NOT NULL, -- 日歷日期
  calendar_year  INT NOT NULL, -- 日歷年
  calendar_month INT NOT NULL, -- 日歷月
  calendar_day   INT NOT NULL, -- 日歷日
  day_of_year    INT NOT NULL, -- 當(dāng)年中的第幾天
  day_of_month   INT NOT NULL, -- 當(dāng)月中的第幾天
  day_of_week    INT NOT NULL, -- 一周中的第幾天（星期天為1）
  is_work_day    VARCHAR(1) DEFAULT 'Y' NOT NULL , -- 是否工作日
  CONSTRAINT uk_calendar UNIQUE (calendar_date)
);

--生成2022-01-01開始的一年數(shù)據(jù)
INSERT INTO calendar
WITH RECURSIVE c(n, dt, y, m, d, doy, dom, dow, work_day) AS (
SELECT 1, '2022-01-01', year('2022-01-01'), month('2022-01-01'), day('2022-01-01'),
       dayofyear('2022-01-01'), dayofmonth('2022-01-01'), dayofweek('2022-01-01'),
       CASE WHEN dayofweek('2022-01-01') IN (2,3,4,5,6) THEN 'Y' ELSE 'N' END work_day
UNION ALL
SELECT n+1,dt + INTERVAL '1' DAY,year(dt + INTERVAL '1' DAY), month(dt + INTERVAL '1' DAY), day(dt + INTERVAL '1' DAY),
       dayofyear(dt + INTERVAL '1' DAY), dayofmonth(dt + INTERVAL '1' DAY), dayofweek(dt + INTERVAL '1' DAY),
       CASE WHEN dayofweek(dt + INTERVAL '1' DAY) IN (2,3,4,5,6) THEN 'Y' ELSE 'N' END work_day
FROM c
WHERE n<365
)
SELECT *
FROM c;
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末豹悬，一起剝皮案震驚了整個濱河市，隨后出現(xiàn)的幾起案子胯究，更是在濱河造成了極大的恐慌沿癞，老刑警劉巖，帶你破解...
    沈念sama閱讀 221,331評論 6贊 515
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件吮铭，死亡現(xiàn)場離奇詭異时迫，居然都是意外死亡，警方通過查閱死者的電腦和手機谓晌，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 94,372評論 3贊 398
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進店門掠拳，熙熙樓的掌柜王于貴愁眉苦臉地迎上來，“玉大人纸肉，你說我怎么就攤上這事溺欧。” “怎么了柏肪？”我有些...
    開封第一講書人閱讀 167,755評論 0贊 360
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵姐刁，是天一觀的道長。 經(jīng)常有香客問我烦味，道長聂使，這世上最難降的妖魔是什么？ 我笑而不...
    開封第一講書人閱讀 59,528評論 1贊 296
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任谬俄，我火速辦了婚禮柏靶，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘凤瘦。我一直安慰自己，他們只是感情好案铺，可當(dāng)我...
    茶點故事閱讀 68,526評論 6贊 397
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布蔬芥。 她就那樣靜靜地躺著，像睡著了一般。 火紅的嫁衣襯著肌膚如雪笔诵。 梳的紋絲不亂的頭發(fā)上返吻，一...
    開封第一講書人閱讀 52,166評論 1贊 308
  • 城市分裂傳說
    那天，我揣著相機與錄音乎婿，去河邊找鬼测僵。 笑死，一個胖子當(dāng)著我的面吹牛谢翎，可吹牛的內(nèi)容都是我干的捍靠。 我是一名探鬼主播，決...
    沈念sama閱讀 40,768評論 3贊 421
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼森逮，長吁一口氣：“原來是場噩夢啊……” “哼榨婆！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起褒侧，我...
    開封第一講書人閱讀 39,664評論 0贊 276
  • 萬榮殺人案實錄
    序言：老撾萬榮一對情侶失蹤良风，失蹤者是張志新（化名）和其女友劉穎，沒想到半個月后闷供，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體烟央，經(jīng)...
    沈念sama閱讀 46,205評論 1贊 319
  • ?護林員之死
    正文 獨居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 38,290評論 3贊 340
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年歪脏，在試婚紗的時候發(fā)現(xiàn)自己被綠了疑俭。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點故事閱讀 40,435評論 1贊 352
  • 活死人
    序言：一個原本活蹦亂跳的男人離奇死亡唾糯，死狀恐怖怠硼，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情移怯，我是刑警寧澤香璃，帶...
    沈念sama閱讀 36,126評論 5贊 349
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布，位于F島的核電站舟误，受9級特大地震影響葡秒，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜嵌溢，卻給世界環(huán)境...
    茶點故事閱讀 41,804評論 3贊 333
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一眯牧、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧赖草，春花似錦学少、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,276評論 0贊 23
  • 一樁弒父案版确，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽扣囊。三九已至，卻和暖如春绒疗，著一層夾襖步出監(jiān)牢的瞬間侵歇，已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 33,393評論 1贊 272
  • 情欲美人皮
    我被黑心中介騙來泰國打工吓蘑， 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留惕虑，地道東北人。 一個月前我還...
    沈念sama閱讀 48,818評論 3贊 376
  • 代替公主和親
    正文 我出身青樓磨镶，卻偏偏與公主長得像溃蔫，于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子棋嘲，可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 45,442評論 2贊 359

推薦閱讀更多精彩內(nèi)容