311. 打標簽合并
山西 交城 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()
1.-- explode的使用
select explode(split(concat_ws(',','1','2','3','4','5','6','7','8','9'),',')) ;
col
1
2
3
4
5
6
7
8
9
關(guān)于explode的用法其實質(zhì)上就是行轉(zhuǎn)列,把一行轉(zhuǎn)為多行
關(guān)于concat_ws的用法是第一個字段是分隔符,后split進行切分
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)可能是版本問題導致,所以我就使用嵌套一層select來處理了
- 說明
lateral view explode() as temp 相當于生成了一張?zhí)摂M表,后與原表進行笛卡爾積
對于這兩個UDTF函數(shù)的難點在于理解虛擬表的邏輯,以及在處理中使用正則表達式來獲取正確的邏輯
參考文檔,這里是SQL復雜處理的核心函數(shù),掌握了這些就非常nice
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
313. 同比環(huán)比
如上,按月份聚合統(tǒng)計不同業(yè)務的同比/環(huán)比情況
這個SQL就是之前conf上寫的SQL,這里復制一下,具體的看那篇博客即可
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 ('機票', '酒店', '度假')
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 ('機票', '酒店', '度假')
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主要用了如下知識點:標簽,lead偏移,order by復合排序
314. 列轉(zhuǎn)行
上邊同比環(huán)比的超級簡化版,簡單復習一下列轉(zhuǎn)行,注意max(case when的時候不要使用else null 可能會出問題)
對于行轉(zhuǎn)列,事實上就是對數(shù)據(jù)結(jié)果的處理,而關(guān)于explode的用法其實質(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ù)的簡單應用,寫到這里我想起來了,sum等開窗函數(shù)可以限制窗口的大小,好久沒用了
數(shù)據(jù)
id NUM
1 5
2 11
3 0
4 -2
5 2
6 9
7 1
8 -4
9 -7
要求當NUM中的數(shù)同時大于上下兩行數(shù)據(jù),返回是,當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ù)字的值以及其個數(shù),求取中位數(shù),在此表中,數(shù)字為0,0,0,0,0,0,0,1,2,2,2.3,所以中位數(shù)為(0+0)/2,這道題目在leedcode上有,記得之前寫過一遍
0 7
1 1
2 3
3 1
create table if not exists c0629(
Number int,Frequency INT
);
insert into c0629 values (0,7),(1,1),(2,3),(3,1);
請編寫一個查詢來查找所有數(shù)字的中位數(shù)并將結(jié)果命名為 median 。注意：什么是中位數(shù)置尔？當一串數(shù)字是奇數(shù)個時，例如8,3,5,1,4。我們按順序排列后為：1,3,4,5,8。那么4就是中位數(shù)
當一串數(shù)字為偶數(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的使用方法,多項獲取,一次解析獲取全部的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ù),其實很簡單,就是使用spark寫json處理邏輯,打包上傳即可,難點是怎么高效處理(對嵌套的處理,使用的簡潔性,但是一般公司都開發(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
(
機臺號 int,
姓名 char(20),
班組 int
);
INSERT INTO `weird_order` (`機臺號`, `姓名`, `班組`)
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);
這里的難點就是要對姓名排序,使用row_number來獲取編號的最小值,就是姓名的新序號
SELECT
t.機臺號,
t.姓名,
t.班組
FROM
weird_order t
JOIN
(SELECT 姓名, MIN(原始序號) AS 新序號
FROM (SELECT *, row_number () over () AS '原始序號'
FROM
weird_order) t
GROUP BY 姓名) tt
ON tt.姓名 = t.姓名
ORDER BY 新序號,
班組,
機臺號;
319. 打印成績單
有兩個表：Students 和 Grades，Students 記錄了學生的分數(shù)翁逞，Grades 存儲了分數(shù)和績點的對應關(guān)系肋杖。
Students 包含了三個字段：ID、Name（姓名）挖函、Marks（分數(shù)）状植。
Grades 有三個字段：Grade（績點）、Min_Mark（最小分值）怨喘、Max_Mark（最大分值）
根據(jù)這兩張表津畸，生成一份學習成績單，這份成績單要包含這三個字段：Name必怜、Grade肉拓、Mark 。
成績單需要滿足以下幾個要求：
績點低于 8 的學生不顯示名字梳庆，使用 NULL 代替暖途。
成績單都得先按照績點降序排序，對于績點相同的記錄膏执，如果績點 >= 8驻售，就再按照姓名的字母順序排序；如果績點 < 8 更米，就再按照分數(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)
留一個思考題，如果把排序的條件“如果績點 < 8 ，就再按照分數(shù)升序排序”中的“升序排序”改成“降序排序”迟几，你會怎么做消请？(直接加一個負號?)
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,'%');
