SQL練習題二十四-每月十題(三)

281.獲取定長連續(xù)子序列

create table savior (
     id int , status int
);

insert  into savior values (1,1);
insert  into savior values (2,1);
insert  into savior values (3,0);
insert  into savior values (4,0);
insert  into savior values (5,0);
insert  into savior values (6,1);
insert  into savior values (7,0);
insert  into savior values (8,0);
insert  into savior values (9,0);
insert  into savior values (10,0);
insert  into savior values (11,0);
insert  into savior values (12,1);
insert  into savior values (13,1);
insert  into savior values (14,0);
insert  into savior values (15,0);

要求：從 savior 表中獲取狀態(tài)為 0 的 id狈定，并且這些 id 能夠組成長度為 3 的連續(xù)子序列擂涛。

提供一種寫法,這種題目在上一篇中有類似的題目,發(fā)現(xiàn)最后一行是不符合題意的,怎么有效率過濾掉是難題....想不到確實不好求....

select
concat(id-1,'---',id+1)
from (
         select id,
                status,
                max(status) over (order by id rows between 1 preceding and 1 following ) as max_number
         from savior
     ) t1
where  max_number =0 ;

方法二:使用自連接也可以求取,答案提供的是row_number獲取重復的便簽,使用自連接來獲取連續(xù)的值

WITH cte AS 
(SELECT 
  *,
  row_number() over (
ORDER BY id) AS rn 
FROM
  savior 
WHERE STATUS = 0) 
SELECT 
  CONCAT_WS('~', a.id, b.id) AS subseq 
FROM
  cte a 
  INNER JOIN cte b 
    ON a.id + 2 = b.id 
    AND a.rn + 2 = b.rn

方法三.使用偏移量函數(shù)來求取
學習一下max()等窗口函數(shù)嵌套if等,到底判斷的是什么???例如

   select id,
                status,
                max(if(status = 1  , 5,status)) over (order by id rows between 1 preceding and 1 following ) as max_number
         from savior

只要max中有一個數(shù)是滿足的那么結果就是5,!!!!!!

282.動態(tài)規(guī)劃

有100天的商品價格,按照買入賣出的順序,怎么操作能賺得最多的價值,以及是多少

create table dtgh (
        data int
);
insert into dtgh values (1);
insert into dtgh values (3);
insert into dtgh values (4);
insert into dtgh values (10);
insert into dtgh values (9);
insert into dtgh values (7);
insert into dtgh values (12);
insert into dtgh values (1);
insert into dtgh values (2);
insert into dtgh values (1);
insert into dtgh values (1);

這是一道動態(tài)規(guī)劃題,使用SQL怎么寫

select
concat(min(data),',',max(data)) ,max(data) - min(data)
from (
         select data,
                sum(`lag`) over (order by rn ) as money
         from (
                  select data,
                         if(data - num < 0, 1, 0) as `lag`,
                         row_number() over ()     as rn
                  from (
                           select data,
                                  lag(data, 1, data + 1) over () as num
                           from dtgh
                       ) t1
              ) t2
     ) t3
group by money
having count(1) > 1 and ( max(data) - min(data) > 0  );

283.層級查詢(一)

在工作中都遇到過存在層次關系的數(shù)據(jù)表祷杈，典型的例子諸如菜單表（多級菜單）嫌术、用戶表（擁有上下級關系）、商品類目表（多級類目）

 empno  ename      mgr  
------  ------  --------
  7369  SMITH       7902
  7499  ALLEN       7698
  7521  WARD        7698
  7566  JONES       7839
  7654  MARTIN      7698
  7698  BLAKE       7839
  7782  CLARK       7839
  7788  SCOTT       7566
  7839  KING      (NULL)
  7844  TURNER      7698
  7876  ADAMS       7788
  7900  JAMES       7698
  7902  FORD        7566
  7934  MILLER      7782

其中氮块，mgr 為 NULL 表明該員工沒有上級領導吼野。
我們要把每個員工的所有上級領導都找出來校哎，實現(xiàn)的效果如下：

 empno  ename   path                  
------  ------  ----------------------
  7369  SMITH   ->FORD->JONES->KING   
  7499  ALLEN   ->BLAKE->KING         
  7521  WARD    ->BLAKE->KING         
  7566  JONES   ->KING                
  7654  MARTIN  ->BLAKE->KING         
  7698  BLAKE   ->KING                
  7782  CLARK   ->KING                
  7788  SCOTT   ->JONES->KING         
  7839  KING                          
  7844  TURNER  ->BLAKE->KING         
  7876  ADAMS   ->SCOTT->JONES->KING  
  7900  JAMES   ->BLAKE->KING         
  7902  FORD    ->JONES->KING         
  7934  MILLER  ->CLARK->KING

對于編號為 7369 的 SMITH，他的直屬領導的編號是 7902瞳步，姓名叫做 FORD闷哆；FORD 的直屬領導叫做 JONES，編號為 7566单起；編號為 7566 的直屬領導是編號為 7839 的 KING抱怔，而 KING 沒有直屬領導。因此嘀倒，SMITH 的上級領導的關系鏈構成：->FORD->JONES->KING

WITH RECURSIVE leader_path(empno, ename, mgr, path) AS 
(SELECT 
  empno,
  ename,
  mgr,
  CAST('' AS CHAR(100)) AS path 
FROM
  emp 
UNION ALL 
SELECT 
  a.empno,
  a.ename,
  b.mgr,
  CONCAT(
    a.path,
    IFNULL(CONCAT('->', b.ename), '')
  ) 
FROM
  leader_path a 
  LEFT JOIN emp b 
    ON a.mgr = b.empno 
WHERE b.empno IS NOT NULL) 
SELECT 
  empno,
  ename,
  path 
FROM
  leader_path 
WHERE mgr IS NULL 
ORDER BY 1

在遞歸中一定要加入終止條件屈留，本 SQL 的終止條件是 WHERE b.empno IS NOT NULL局冰；
遇到字符串拼接需要提前設置該字段的長度，對應到 SQL 中的操作是 CAST('' AS CHAR(100))灌危；
遞歸會生成中間結果康二，我們要把中間結果過濾掉，WHERE mgr IS NULL 就是只獲取最終的結果乍狐。

284.層級查詢(二)

在mysql中實現(xiàn)層次查詢的兩種方式赠摇。層級查詢(一)舉的示例是獲取從葉子點到根節(jié)點的路徑，層級查詢(二)要實現(xiàn)的是從根節(jié)點找到所有葉子節(jié)點浅蚪。

WITH RECURSIVE leader_path (empno, ename, mgr, lv) AS 
(SELECT 
  empno,
  ename,
  mgr,
  1 AS lv
FROM
  emp WHERE mgr IS NULL
UNION ALL 
SELECT 
  b.empno,
  b.ename,
  b.mgr,
  lv + 1
FROM
  leader_path a 
  INNER JOIN emp b 
    ON a.empno = b.mgr ) 
SELECT 
  empno,
  ename,
  lv 
FROM
  leader_path 
ORDER BY 1

結果如下:

 empno  ename       lv  
------  ------  --------
  7369  SMITH          4
  7499  ALLEN          3
  7521  WARD           3
  7566  JONES          2
  7654  MARTIN         3
  7698  BLAKE          2
  7782  CLARK          2
  7788  SCOTT          3
  7839  KING           1
  7844  TURNER         3
  7876  ADAMS          4
  7900  JAMES          3
  7902  FORD           3
  7934  MILLER         3

285.獲取一行中多個字段的最大值

    id      v1      v2      v3  
------  ------  ------  --------
     1     100      80       102
     2       2     -20        -1
     3     999      12       111
     4    1234    2222      -123
     5     871     888       666
     6    -210       9      1024
     7       0      -1         0
     8       2       2         2

查詢結果

    id   v_max  
------  --------
     1       102
     2         2
     3       999
     4      2222
     5       888
     6      1024
     7         0
     8         2

方法一:GREATEST()函數(shù)

SELECT 
  id,
  GREATEST(v1, v2, v3) AS v_max 
FROM
  chaos

方法二:嵌套的 IF 語句

v12 = IF(v1 > v2, v1, v2)
v_max = IF(v12 > v3, v12, v3)

即:

--有點復雜.....
SELECT 
  id,
  IF(
    IF(v1 > v2, v1, v2) > v3,
    IF(v1 > v2, v1, v2),
    v3
  ) AS v_max 
FROM
  chaos

方法三:使用union all 扁平化

WITH chaos_union AS 
(SELECT 
  id,
  v1 AS v 
FROM
  chaos 
UNION ALL 
SELECT 
  id,
  v2 AS v 
FROM
  chaos 
UNION ALL 
SELECT 
  id,
  v3 AS v 
FROM
  chaos)
SELECT 
  id,
  MAX(v) AS v_max 
FROM
  chaos_union 
GROUP BY id

286.分位函數(shù)

hive中求中位數(shù),我一般是用正序和逆序來解決,這里提供hive中的分位函數(shù)來求取中位數(shù)

https://blog.csdn.net/Jarry_cm/article/details/82185576?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522161631858216780265426998%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=161631858216780265426998&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~

percentile：percentile(col, p) col是要計算的列（值必須為int類型）藕帜，p的取值為0-1，若為0.2惜傲，那么就是2分位數(shù)洽故，依次類推。
percentile_approx：percentile_approx(col, p)盗誊。列為數(shù)值類型都可以时甚。
percentile_approx還有一種形式percentile_approx(col, p，B)哈踱，參數(shù)B控制內(nèi)存消耗的近似精度荒适，B越大，結果的精度越高开镣。默認值為10000刀诬。當col字段中的distinct值的個數(shù)小于B時，結果就為準確的百分位數(shù)

287.打標簽(1)

下面兩題是攜程數(shù)倉的面試題

create table if not exists  xiechen  (
     time_day  date,product varchar(4) ,profit int
);

insert into xiechen values ('2021-01-01','A',2);

insert into xiechen values ('2021-01-02','A',3);
insert into xiechen values ('2021-01-03','A',4);
insert into xiechen values ('2021-01-04','A',5);
insert into xiechen values ('2021-01-01','B',2);
insert into xiechen values ('2021-01-02','B',4);
insert into xiechen values ('2021-01-03','B',3);
insert into xiechen values ('2021-01-04','B',4);
insert into xiechen values ('2021-01-01','B',5);

求每組收益連續(xù)增加3天以上的天數(shù)

select concat(min(time_day),'--',max(time_day)) , max(product)
from (
         select time_day,
                product,
                profit,
                sum(lag_rn) over (partition by product order by time_day) as rn
         from (
                  select time_day,
                         product,
                         profit,
                         if(profit - lag(profit, 1, profit) over (partition by product) <= 0, 1, 0) as lag_rn
                  from xiechen) t1
     )  t2
  group by product,rn
  having  count(rn) >= 3;

288.打標簽(2)

題目:在第一題的基礎上,求日期是連續(xù)的且收益是連續(xù)3天遞增的
解答:
在第一題的基礎上在group by 后的having后增加:having count(rn) >= 3 and datediff(max(time_day),min(time_day)) + 1 = count(rn);
只要datediff(max(time_day),min(time_day)) 間隔數(shù)等于總的行數(shù)就是答案

select concat(min(time_day),'--',max(time_day)) , max(product)
from (
         select time_day,
                product,
                profit,
                sum(lag_rn) over (partition by product order by time_day) as rn
         from (
                  select time_day,
                         product,
                         profit,
                         if(profit - lag(profit, 1, profit) over (partition by product) <= 0, 1, 0) as lag_rn
                  from xiechen) t1
     )  t2
  group by product,rn
  having  count(rn) >= 3 and datediff(max(time_day),min(time_day)) + 1 = count(rn);

289.魔力貓盒面試題

主糧,渴望,0.45
主糧,哈根紐翠斯,0.15
主糧,愛肯納,0.40
罐頭,儀親,0.05
罐頭,麥克勞德醫(yī)生,0.8
罐頭,happy100,0.15
零食,儀親,0.24
零食,益智選,0.33
零食,mikbone,0.20
零食,巔峰,0.23

要求：求取amount_percent從大到小邪财，累計和大于等于75%,的情況陕壹，且以大于等于75%為界限

select cat_name,brand_name,amount_percent from(
   select
   cat_name,brand_name,amount_percent,calculate_percent,
   lag(calculate_percent,1,0) over(partition by cat_name order by calculate_percent) as lag_persent
   from
        (
         select
         cat_name,brand_name,amount_percent,
         sum(amount_percent) over(partition by cat_name order by amount_percent desc) as calculate_percent    
         from group_precent_top_n) as a1
      ) as a2
where (calculate_percent >= 0.75 and lag_persent < 0.75) or
(calculate_percent < 0.75 and lag_persent < 0.75)
;

其實不必這么寫,這道題考查是溝通需求,過濾出特殊的數(shù)據(jù)行或者改寫需求
提供其他方法:

使用hive中的桶函數(shù)分20個桶
如上面的解答方法使用sum窗口來求
使用row_number() 和count() 窗口來求
使用自連接來求

290.簡單用戶畫像

有表sale:order_id 訂單id,唯一字段 user_id 用戶id product_id 訂單id create_time 訂單的創(chuàng)建時間 ,product_num 訂單數(shù)
有表user_info user_id 用戶id sex age

求:使用一條SQL生成完整的用戶畫像包含,用戶id sex age d7order_num d14_order_num.后面兩個字段分別為近七天的訂單數(shù),近14天的訂單數(shù)

使用sum(if)來寫

select t1.user_id ,
       sex,
       age,
       sum(if(datediff('2021-3-21',create_time)<=7,product_num,0)) as d7order_num ,
       sum(if(datediff('2021-3-21',create_time)<=14,product_num,0)) as d14order_num
from user_info t1 left join sale t2 on t1.user_id = t2.user_id
group by t1.user_id, sex, age;

使用join來寫

select t1.user_id,sex,age,t2.d14order_num,t3.d7order_num
from user_info t1 left join (
      select  user_id ,sum(product_num) as d14order_num
             from sale
             where datediff('2021-3-21',create_time)<=14
             group by user_id
    ) t2 on t1.user_id = t2.user_id
left join (
        select  user_id ,sum(product_num) as d7order_num
              from sale
              where datediff('2021-3-21',create_time)<= 7
              group by user_id
    ) t3 on t1.user_id = t3.user_id;

最后編輯于：2021.03.21 19:37:27

?著作權歸作者所有,轉載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末，一起剝皮案震驚了整個濱河市树埠，隨后出現(xiàn)的幾起案子糠馆，更是在濱河造成了極大的恐慌，老刑警劉巖怎憋，帶你破解...
沈念sama閱讀 217,826評論 6贊 506
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件又碌，死亡現(xiàn)場離奇詭異，居然都是意外死亡绊袋，警方通過查閱死者的電腦和手機赠橙，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 92,968評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來愤炸，“玉大人期揪，你說我怎么就攤上這事」娓觯” “怎么了凤薛？”我有些...
開封第一講書人閱讀 164,234評論 0贊 354
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵姓建，是天一觀的道長。經(jīng)常有香客問我缤苫，道長速兔，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 58,562評論 1贊 293
?港島之戀（遺憾婚禮）
正文為了忘掉前任活玲，我火速辦了婚禮涣狗，結果婚禮上，老公的妹妹穿的比我還像新娘舒憾。我一直安慰自己镀钓，他們只是感情好，可當我...
茶點故事閱讀 67,611評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布镀迂。她就那樣靜靜地躺著丁溅，像睡著了一般。火紅的嫁衣襯著肌膚如雪探遵。梳的紋絲不亂的頭發(fā)上窟赏，一...
開封第一講書人閱讀 51,482評論 1贊 302
城市分裂傳說
那天，我揣著相機與錄音箱季，去河邊找鬼涯穷。笑死，一個胖子當著我的面吹牛藏雏，可吹牛的內(nèi)容都是我干的拷况。我是一名探鬼主播，決...
沈念sama閱讀 40,271評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼诉稍，長吁一口氣：“原來是場噩夢啊……” “哼蝠嘉！你這毒婦竟也來了最疆？” 一聲冷哼從身側響起杯巨，我...
開封第一講書人閱讀 39,166評論 0贊 276
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎努酸，沒想到半個月后服爷，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 45,608評論 1贊 314
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡获诈，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 37,814評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年仍源，在試婚紗的時候發(fā)現(xiàn)自己被綠了。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片舔涎。...
茶點故事閱讀 39,926評論 1贊 348
活死人
序言：一個原本活蹦亂跳的男人離奇死亡笼踩，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出亡嫌，到底是詐尸還是另有隱情嚎于，我是刑警寧澤掘而，帶...
沈念sama閱讀 35,644評論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站于购，受9級特大地震影響袍睡，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜肋僧，卻給世界環(huán)境...
茶點故事閱讀 41,249評論 3贊 329
男人毒藥：我在死后第九天來索命
文/蒙蒙一斑胜、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧嫌吠，春花似錦止潘、人聲如沸。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,866評論 0贊 22
一樁弒父案覆山，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽。三九已至泥栖，卻和暖如春簇宽，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背吧享。一陣腳步聲響...
開封第一講書人閱讀 32,991評論 1贊 269
情欲美人皮
我被黑心中介騙來泰國打工魏割，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留，地道東北人钢颂。一個月前我還...
沈念sama閱讀 48,063評論 3贊 370
代替公主和親
正文我出身青樓钞它，卻偏偏與公主長得像，于是被迫代替她去往敵國和親殊鞭。傳聞我的和親對象是個殘疾皇子遭垛，可洞房花燭夜當晚...
茶點故事閱讀 44,871評論 2贊 354