1個SQL題，1個場景題，會有點難度！

SQL題

該SQL題大量涉及到row_number，case when，group by等高級用法，有一定的實用價值，總結出來，供日后參考

Question.1：

分組匯總

給定篩選條件

SELECT

Sales_Month

,Customer_ID

,Amount

FROM

(

SELECT

MONTH(Sales_Date) AS Sales_Month

,Customer_ID

,sum(Amount) AS Amount

FROM

Sales

GROUP BY

Sales_Month, Customer_ID

) AS A

WHERE

A.Amount BETWEEN 2000 AND 10000

Question.2：

全集合保留最大值所在行(針對天做處理)

為月維度下給定序列號(針對月做處理)

Group By + Case When 抽取特定值為一個維度

SELECT

B.Sales_month

,B.Customer_ID

,max( CASE WHEN B.nums = 1 THEN B.item ELSE NULL END ) AS Item1

,max( CASE WHEN B.nums = 2 THEN B.item ELSE NULL END ) AS Item2

,max( CASE WHEN B.nums = 3 THEN B.item ELSE NULL END ) AS Item3

FROM

(

SELECT

A.Sales_month

,A.Customer_ID

,A.item

,row_number() over (PARTITION BY A.Sales_month, A.Customer_ID

ORDER BY A.Sales_Date ) AS nums

FROM

(

SELECT

concat(YEAR (Sales_Date), '-',

MONTH(Sales_Date)) AS Sales_month

,Sales_Date

,Customer_ID

,item

,row_number() over (PARTITION BY Sales_Date, Customer_ID

ORDER BY Amount DESC ) AS nums

FROM

sales

) AS A

WHERE

A.nums = 1

) AS B

GROUP BY

B.Sales_month

,B.Customer_ID

ORDER BY

B.Sales_month

,B.Customer_ID

Question.3：

分別選取兩個月的集合，對item分類匯總

連接集合，并計算銷售額的差值

輸出類別，并根據差值跟定序號

SELECT

row_number() over(ORDER BY A.decrease_num DESC) AS Rank_

,A.Item as Item

,A.decrease_num AS MoM_Decrease

FROM

(

select

L.item

,(L.Amount-R.Amount) as decrease_num

from

(

SELECT

item

,sum(Amount) AS Amount

FROM

sales

WHERE

year(Sales_Date) =2018 and month(Sales_Date)=7

GROUP BY

Item

) AS L

inner join

(

SELECT

item

,sum(Amount) AS Amount

FROM

sales

WHERE

year(Sales_Date) =2018 and month(Sales_Date)=8

GROUP BY

Item

) AS R

ON L.item=R.item

) AS A

ORDER BY

Rank_ ASC

LIMIT 10

Question.4：

連續的表示方式：8月的每一天相對于7月的某一天以+1的方式線性增長，排序也是以+1的方式線性增長，連續情況下兩者之間的差值相等，對該差值計數即可知道不同的連續天數

計算日期排序的序號和日期相對于7月31日的差值

針對差值分類匯總，計算連續天數和起始日期

給出連續天數大于等于3的類別

SELECT

D.Customer_ID

,D.running_days

,D.start_date

,D.end_date

FROM

(

SELECT

C.Customer_ID

,C.diff_value

,min( C.Sales_Date ) AS start_date

,max( C.Sales_Date ) AS end_date

,count( 1 ) AS running_days

FROM

(

select

B.Customer_ID

,B.Sales_Date

,B.day_interval

,CONVERT(B.day_rank, SIGNED) as day_rank

,(B.day_interval-CONVERT(B.day_rank,SIGNED)) as diff_value

from

(

SELECT

A.Customer_ID

,A.Sales_Date

,datediff( A.Sales_Date, '2018-07-31' ) AS day_interval

,row_number( ) over(PARTITION BY A.Customer_ID

ORDER BY A.Sales_Date ) AS day_rank

FROM

(

select

distinct Sales_Date,Customer_ID

from

sales

) as A

where

Sales_Date>='2018-08-01'

and Sales_Date<='2018-08-31'

) AS B

) as C

GROUP BY

C.Customer_ID

,C.diff_value

) as D

where

D.running_days>=3

ORDER BY

D.Customer_ID

,D.start_date

場景題

有一個列的數據格式是1,2,500,4以逗號分隔數字，創建函數計算小于100數字的平均值

drop FUNCTION if EXISTS `AVG_answser_intval`;

delimiter $

CREATE DEFINER = CURRENT_USER FUNCTION `AVG_answser_intval`(Str VARCHAR(255))

RETURNS DECIMAL(8,2)

DETERMINISTIC

BEGIN

DECLARE Str_sum DECIMAL(8,2) DEFAULT 0.00;

DECLARE Str_con int DEFAULT 0;

DECLARE tmp_dot int;

DECLARE tmp_dec DECIMAL(8,2);

DECLARE result DECIMAL(8,2) DEFAULT 0.00;

while Str<>'' DO

set tmp_dot=LOCATE(',',Str);

IF tmp_dot<>0 THEN

set tmp_dec =CAST(SUBSTR(Str,1,tmp_dot-1)AS DECIMAL(8,2));

set Str_sum=Str_sum+if(tmp_dec <100,tmp_dec,0.0);

set Str=SUBSTR(Str,tmp_dot+1,LENGTH(Str)-tmp_dot);

set Str_con = Str_con+if(tmp_dec <100,1,0);

ELSE

set tmp_dec =CAST(Str AS DECIMAL(8,2));

set Str_sum=Str_sum+if(tmp_dec<100,tmp_dec,0.0);

set Str='';

set Str_con = Str_con+if(tmp_dec <100,1,0);

END IF;

END while;

set result = IF(Str_con>0,ROUND(Str_sum/Str_con,2),0);

RETURN result;

END$

delimiter ;