c mysql使用场景_Mysql 场景

1个SQL题,1个场景题,会有点难度!

SQL题

699e901af112530385dc9137aed9cd8b.png

该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 ;

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值