中文字幕亚洲一二三区,日韩长久视频

311. 打標(biāo)簽合并

山西  交城  100 平安:40
山西  交城  100 非平安:60

合并成如下
山西  交城  100 平安:40  非平安:60

create table a0628 (
    Secondaryinstitutions varchar(4) ,
    Tertiaryinstitutions   varchar(4),
    num int,
    sources varchar(10)
);
insert into a0628 values ('山西','交城',100,'平安:40'),('山西','交城',100,'非平安:60');

select
Secondaryinstitutions, Tertiaryinstitutions, num,
       MAX(CASE  WHEN tag = '平安' then sources end) as '平安',
       MAX(CASE  WHEN tag = '非平安' then sources end) as '非平安'
from
     (
select
*,if ( left(sources,2)  = '平安' ,'平安' ,'非平安' ) as tag
from a0628 )  t1
group by Secondaryinstitutions,Tertiaryinstitutions,num;

312. explode()和posexplode()

參考文章
https://blog.csdn.net/dzysunshine/article/details/101110467?utm_medium=distribute.pc_relevant.none-task-blog-2~

1.-- explode的使用

select explode(split(concat_ws(',','1','2','3','4','5','6','7','8','9'),',')) ;

關(guān)于explode的用法其實(shí)質(zhì)上就是行轉(zhuǎn)列,把一行轉(zhuǎn)為多行
關(guān)于concat_ws的用法是第一個(gè)字段是分隔符,后split進(jìn)行切分

2. -- LATERAL VIEW POSEXPLODE的使用

WITH a AS (
        SELECT '2021-04-20' AS depart_time, '2021-04-10' AS arrive_time
        UNION ALL
        SELECT '2021-03-11', '2021-03-09'
    )
SELECT tf.*
FROM a
    LATERAL VIEW POSEXPLODE
 (SPLIT(SPACE(DATEDIFF(TO_DATE(a.depart_time), TO_DATE(a.arrive_time)) - 1), ' ')) tf

3. -- LATERAL VIEW explode的使用(explode(array (or map))

示例一

WITH x AS (
        SELECT 'a' AS id, '12_14_15' AS age
        UNION ALL
        SELECT 'b', '12_14_15'
    )
SELECT age_single
FROM x
    LATERAL VIEW explode(split(x.age, '_')) temp AS age_single

示例二,這里不使用with來展示,是由于這邊的spark引擎不支持在with中使用array類型

SELECT col
FROM (
    SELECT 'UU', array('A', 'B', 'C') AS arr
) t
    LATERAL VIEW explode(t.arr) tf AS col

3.示例三

select t1.*,sp  from  (select 1 ) as t1    
lateral view explode(split(concat_ws(',','1','2','3','4','5','6','7','8','9'),',')) t as sp

這里本來是使用with的,但是發(fā)現(xiàn)可能是版本問題導(dǎo)致,所以我就使用嵌套一層select來處理了

說明
lateral view explode() as temp 相當(dāng)于生成了一張?zhí)摂M表,后與原表進(jìn)行笛卡爾積
對(duì)于這兩個(gè)UDTF函數(shù)的難點(diǎn)在于理解虛擬表的邏輯,以及在處理中使用正則表達(dá)式來獲取正確的邏輯

參考文檔,這里是SQL復(fù)雜處理的核心函數(shù),掌握了這些就非常nice
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

313. 同比環(huán)比

如上,按月份聚合統(tǒng)計(jì)不同業(yè)務(wù)的同比/環(huán)比情況
這個(gè)SQL就是之前conf上寫的SQL,這里復(fù)制一下,具體的看那篇博客即可

select
lag,j_lag,
MAX(case when substring(j_month,6,7) = '01' then relative  end ) as m1,
MAX(case when substring(j_month,6,7) = '02' then relative  end ) as m2,
MAX(case when substring(j_month,6,7) = '03' then relative  end ) as m3,
MAX(case when substring(j_month,6,7) = '04' then relative  end ) as m4,
MAX(case when substring(j_month,6,7) = '05' then relative  end ) as m5,
MAX(case when substring(j_month,6,7) = '06' then relative  end ) as m6,
MAX(case when substring(j_month,6,7) = '07' then relative  end ) as m7,
MAX(case when substring(j_month,6,7) = '08' then relative  end ) as m8,
MAX(case when substring(j_month,6,7) = '09' then relative  end ) as m9,
MAX(case when substring(j_month,6,7) = '10' then relative  end ) as m10,
MAX(case when substring(j_month,6,7) = '11' then relative  end ) as m11,
MAX(case when substring(j_month,6,7) = '12' then relative  end ) as m12
from 
(
SELECT lag, '環(huán)比' AS j_lag, j_month
    , (order_price - lead_price_relative) * 1.0 / lead_price_relative AS relative
FROM (
    SELECT lag, j_month, order_price
        , lead(order_price, 1, NULL) OVER (PARTITION BY lag ORDER BY j_month DESC) AS lead_price_relative
    FROM (
        SELECT concat(lag_country, bu) AS lag, j_month, order_price
        FROM (
            SELECT bu
                , CASE 
                    WHEN to_country_id = 1 THEN '國內(nèi)'
                    WHEN to_country_id <> 1 THEN '國外'
                END AS lag_country, substring(d, 0, 7) AS j_month
                , round(SUM(order_price), 0) AS order_price
            FROM table
            WHERE d >= '2019-01-01'
                AND bu IN ('機(jī)票', '酒店', '度假')
                AND d <> '4000-01-01'
            GROUP BY bu, substring(d, 0, 7), CASE 
                    WHEN to_country_id = 1 THEN '國內(nèi)'
                    WHEN to_country_id <> 1 THEN '國外'
                END
        ) a
    ) b
    WHERE lag IS NOT NULL
) c
UNION ALL
(
SELECT lag, '同比' , j_month
    , (order_price - lead_price_basis) * 1.0 / lead_price_basis 
FROM (
    SELECT lag, j_month, order_price
        , lead(order_price, 1, NULL) OVER (PARTITION BY lag ORDER BY substring(j_month, 6, 7) DESC,substring(j_month, 0, 4) desc) AS lead_price_basis
    FROM (
        SELECT concat(lag_country, bu) AS lag, j_month, order_price
        FROM (
            SELECT bu
                , CASE 
                    WHEN to_country_id = 1 THEN '國內(nèi)'
                    WHEN to_country_id <> 1 THEN '國外'
                END AS lag_country, substring(d, 0, 7) AS j_month
                , round(SUM(order_price), 0) AS order_price
            FROM table
            WHERE d >= '2019-01-01'
                AND bu IN ('機(jī)票', '酒店', '度假')
                AND d <> '4000-01-01'
            GROUP BY bu, substring(d, 0, 7), CASE 
                    WHEN to_country_id = 1 THEN '國內(nèi)'
                    WHEN to_country_id <> 1 THEN '國外'
                END
        ) a
    ) b
    WHERE lag IS NOT NULL
) c
)
) 
WHERE substring(j_month, 0, 4) = '2021'
GROUP by lag,j_lag
order by substring(lag,3,4),substring(lag,0,2),j_lag

關(guān)于這道SQL主要用了如下知識(shí)點(diǎn):標(biāo)簽,lead偏移,order by復(fù)合排序

314. 列轉(zhuǎn)行

上邊同比環(huán)比的超級(jí)簡化版,簡單復(fù)習(xí)一下列轉(zhuǎn)行,注意max(case when的時(shí)候不要使用else null 可能會(huì)出問題)
對(duì)于行轉(zhuǎn)列,事實(shí)上就是對(duì)數(shù)據(jù)結(jié)果的處理,而關(guān)于explode的用法其實(shí)質(zhì)上就是行轉(zhuǎn)列,把一行轉(zhuǎn)為多行.

2020    1   9.1
2020    2   9.2
2020    3   9.3
2020    4   9.4
2021    1   8.1
2021    2   8.2
2021    3   8.3
2021    4   8.4

將如上轉(zhuǎn)化為
year    M1   M2   M3   M4 
2020    9.1 9.2 9.3 9.4
2021    8.1 8.2 8.3 8.4

create  table if not exists  b0628(
    year varchar(4), month varchar(1) ,amount double
);

INSERT INTO b0628 VALUES
('2020','1',9.1),
('2020','2',9.2),
('2020','3',9.3),
('2020','4',9.4),
('2021','1',8.1),
('2021','2',8.2),
('2021','3',8.3),
('2021','4',8.4);

select
year,
        max(case  when month = 1 then amount end ) as M1,
        max(case  when month = 2 then amount end ) as M2,
        max(case  when month = 3 then amount end ) as M3,
        max(case  when month = 4 then amount end ) as M4
from b0628 group by year order by year ;

315. LAG&LEAD

偏移量函數(shù)的簡單應(yīng)用,寫到這里我想起來了,sum等開窗函數(shù)可以限制窗口的大小,好久沒用了

數(shù)據(jù)
id  NUM
1   5
2   11
3   0
4   -2
5   2
6   9
7   1
8   -4
9   -7

要求當(dāng)NUM中的數(shù)同時(shí)大于上下兩行數(shù)據(jù),返回是,當(dāng)num中的數(shù)據(jù)小于上下兩行中的任意一行,返回否

CREATE TABLE T0611 (
ID INT,
Num INT
);

INSERT INTO T0611 VALUES(1,5);
INSERT INTO T0611 VALUES(2,11);
INSERT INTO T0611 VALUES(3,0);
INSERT INTO T0611 VALUES(4,-2);
INSERT INTO T0611 VALUES(5,2);
INSERT INTO T0611 VALUES(6,9);
INSERT INTO T0611 VALUES(7,1);
INSERT INTO T0611 VALUES(8,-4);
INSERT INTO T0611 VALUES(9,-7);

select
ID,Num,if(Num>lag_1 and Num>lead_1,'是','否') as result
from (
         select id,
                num,
                lag(Num, 1) over (order by id ) as lag_1,
                lead(Num, 1) over (order by ID) as lead_1
         from T0611
     ) t1
order by  id ;

316. 中位數(shù)

表中保存了數(shù)字的值以及其個(gè)數(shù),求取中位數(shù),在此表中,數(shù)字為0,0,0,0,0,0,0,1,2,2,2.3,所以中位數(shù)為(0+0)/2,這道題目在leedcode上有,記得之前寫過一遍

create  table if not exists c0629(
 Number int,Frequency INT
);
insert into  c0629 values (0,7),(1,1),(2,3),(3,1);

請(qǐng)編寫一個(gè)查詢來查找所有數(shù)字的中位數(shù)并將結(jié)果命名為 median 。注意：什么是中位數(shù)？當(dāng)一串?dāng)?shù)字是奇數(shù)個(gè)時(shí)，例如8,3,5,1,4。我們按順序排列后為：1,3,4,5,8。那么4就是中位數(shù)
當(dāng)一串?dāng)?shù)字為偶數(shù)個(gè)時(shí)，例如8,3,5,1,4,2。我們按順序排列后為：1,2,3,4,5,8。那么(3+4)/2=3.5就是中位數(shù)。

select
    avg(n.Number) as median
from
    Numbers as n
where
    n.Frequency >= abs(
        (select sum(Frequency) from Numbers where Number <= n.Number) - 
        (select sum(Frequency) from Numbers where Number >= n.Number)
    )

select avg(number) as median
from
(select Number, frequency,
        sum(frequency) over(order by number asc) as total,
        sum(frequency) over(order by number desc) as total1
from Numbers
order by number asc)as a
where total>=(select sum(frequency) from Numbers)/2
and total1>=(select sum(frequency) from Numbers)/2

可考慮使用其他方法,使用中位數(shù)的定義從位置邏輯和值和上來理解

317. 使用sparksql解析json類型的字符串

-- 方法一,使用spark自帶的函數(shù)get_json_object解析,但是效率比較低

SELECT get_json_object(label_value_text, '$.BUS')
FROM (
SELECT '{"BUS":1,"CAR":1,"DIY":1,"FLT":1,"HTL":1,"PKG":1,"TRN":1,"TTD":1}' AS label_value_text
) t1
LIMIT 10

-- 方法二,from_json的用法

select a.k
from (
select from_json('{"k": "xiecheng", "v": 1.0}','k STRING, v STRING',map("","")) as a
) t1

-- 方法三,json_tuple的使用方法,多項(xiàng)獲取,一次解析獲取全部的json數(shù)據(jù)

SELECT json_tuple(label_value_text, 'BUS','CAR')
FROM (
SELECT '{"BUS":1,"CAR":0,"DIY":1,"FLT":1,"HTL":1,"PKG":1,"TRN":1,"TTD":1}' AS label_value_text
) t1

-- 方法四,自主開發(fā)的自定義函數(shù),其實(shí)很簡單,就是使用spark寫json處理邏輯,打包上傳即可,難點(diǎn)是怎么高效處理(對(duì)嵌套的處理,使用的簡潔性,但是一般公司都開發(fā)了直接用就好了.......)

參考文檔:
https://blog.csdn.net/lsr40/article/details/79399166
https://blog.csdn.net/lsr40/article/details/103020021
https://cloud.tencent.com/developer/article/1451308
https://cloud.tencent.com/developer/article/1032532
https://blog.csdn.net/weixin_38750084/article/details/93498986?utm_medium=distribute.pc_relevant.none-task-blog-2~

318. 怪異的排序

1   張三  1
1   李四  2
2   張三  1
2   李四  2
3   張三  1
3   李四  2
4   王五  1
4   趙柳  2
5   王五  1
5   趙柳  2
6   王五  1
6   趙柳  2
7   麻七  1
7   賴八  2
8   麻七  1
8   賴八  2
9   麻七  1
9   賴八  2

轉(zhuǎn)變?yōu)槿缦?/p>

1   張三  1
2   張三  1
3   張三  1
1   李四  2
2   李四  2
3   李四  2
4   王五  1
5   王五  1
6   王五  1
4   趙柳  2
5   趙柳  2
6   趙柳  2
7   麻七  1
8   麻七  1
9   麻七  1
7   賴八  2
8   賴八  2
9   賴八  2

create table weird_order
(
機(jī)臺(tái)號(hào) int,
姓名 char(20),
班組 int
);
INSERT INTO `weird_order` (`機(jī)臺(tái)號(hào)`, `姓名`, `班組`)
VALUES
  (1, '張三', 1),
  (1, '李四', 2),
  (2, '張三', 1),
  (2, '李四', 2),
  (3, '張三', 1),
  (3, '李四', 2),
  (4, '王五', 1),
  (4, '趙柳', 2),
  (5, '王五', 1),
  (5, '趙柳', 2),
  (6, '王五', 1),
  (6, '趙柳', 2),
  (7, '麻七', 1),
  (7, '賴八', 2),
  (8, '麻七', 1),
  (8, '賴八', 2),
  (9, '麻七', 1),
  (9, '賴八', 2);

這里的難點(diǎn)就是要對(duì)姓名排序,使用row_number來獲取編號(hào)的最小值,就是姓名的新序號(hào)

SELECT
  t.機(jī)臺(tái)號(hào),
  t.姓名,
  t.班組
FROM
  weird_order t
JOIN
    (SELECT 姓名, MIN(原始序號(hào)) AS 新序號(hào)
    FROM (SELECT *, row_number () over () AS '原始序號(hào)'
      FROM
        weird_order) t
    GROUP BY 姓名) tt
    ON tt.姓名 = t.姓名
ORDER BY 新序號(hào),
  班組,
  機(jī)臺(tái)號(hào);

319. 打印成績單

有兩個(gè)表：Students 和 Grades，Students 記錄了學(xué)生的分?jǐn)?shù)，Grades 存儲(chǔ)了分?jǐn)?shù)和績點(diǎn)的對(duì)應(yīng)關(guān)系。
Students 包含了三個(gè)字段：ID、Name（姓名）、Marks（分?jǐn)?shù)）。
Grades 有三個(gè)字段：Grade（績點(diǎn)）、Min_Mark（最小分值）、Max_Mark（最大分值）
根據(jù)這兩張表，生成一份學(xué)習(xí)成績單，這份成績單要包含這三個(gè)字段：Name、Grade、Mark 。

成績單需要滿足以下幾個(gè)要求：
績點(diǎn)低于 8 的學(xué)生不顯示名字，使用 NULL 代替。
成績單都得先按照績點(diǎn)降序排序，對(duì)于績點(diǎn)相同的記錄，如果績點(diǎn) >= 8，就再按照姓名的字母順序排序；如果績點(diǎn) < 8 ，就再按照分?jǐn)?shù)升序排序。

SELECT
  IF(grade >= 8, name, NULL) AS name,
  grade,
  marks
FROM
  Students
  INNER JOIN Grades
    ON marks BETWEEN min_mark
    AND max_mark
ORDER BY grade DESC,
  IF(grade >= 8, name, marks)

留一個(gè)思考題，如果把排序的條件“如果績點(diǎn) < 8 ，就再按照分?jǐn)?shù)升序排序”中的“升序排序”改成“降序排序”，你會(huì)怎么做？(直接加一個(gè)負(fù)號(hào)?)

320. 完全包含

求左邊完全包含右邊的

SQL數(shù)據(jù)庫開發(fā)    數(shù)據(jù)庫
北京  中國
新加坡城    新加坡

CREATE TABLE T0608
(
A VARCHAR(100),
B VARCHAR(100)
);

INSERT INTO T0608 VALUES
('SQL數(shù)據(jù)庫開發(fā)','數(shù)據(jù)庫'),
('北京','中國'),
('新加坡城','新加坡');

select  *   from T0608 where  A like  concat('%',B,'%');

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

SQL練習(xí)題二十七-每月十題(六)

SQL練習(xí)題二十七-每月十題(六)

311. 打標(biāo)簽合并

312. explode()和posexplode()

1.-- explode的使用

2. -- LATERAL VIEW POSEXPLODE的使用

3. -- LATERAL VIEW explode的使用(explode(array (or map))

313. 同比環(huán)比

314. 列轉(zhuǎn)行

315. LAG&LEAD

316. 中位數(shù)

317. 使用sparksql解析json類型的字符串

-- 方法一,使用spark自帶的函數(shù)get_json_object解析,但是效率比較低

-- 方法二,from_json的用法

-- 方法三,json_tuple的使用方法,多項(xiàng)獲取,一次解析獲取全部的json數(shù)據(jù)

-- 方法四,自主開發(fā)的自定義函數(shù),其實(shí)很簡單,就是使用spark寫json處理邏輯,打包上傳即可,難點(diǎn)是怎么高效處理(對(duì)嵌套的處理,使用的簡潔性,但是一般公司都開發(fā)了直接用就好了.......)

318. 怪異的排序

319. 打印成績單

320. 完全包含

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

SQL練習(xí)題二十七-每月十題(六)

311. 打標(biāo)簽合并

312. explode()和posexplode()

1.-- explode的使用

2. -- LATERAL VIEW POSEXPLODE的使用

3. -- LATERAL VIEW explode的使用(explode(array (or map))

313. 同比環(huán)比

314. 列轉(zhuǎn)行

315. LAG&LEAD

316. 中位數(shù)

317. 使用sparksql解析json類型的字符串

-- 方法一,使用spark自帶的函數(shù)get_json_object解析,但是效率比較低

-- 方法二,from_json的用法

-- 方法三,json_tuple的使用方法,多項(xiàng)獲取,一次解析獲取全部的json數(shù)據(jù)

-- 方法四,自主開發(fā)的自定義函數(shù),其實(shí)很簡單,就是使用spark寫json處理邏輯,打包上傳即可,難點(diǎn)是怎么高效處理(對(duì)嵌套的處理,使用的簡潔性,但是一般公司都開發(fā)了直接用就好了.......)

318. 怪異的排序

319. 打印成績單

320. 完全包含

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av