江西做网站,建站公司怎么获客,注册百度账号免费,展示型为主的网站1个SQL题#xff0c;1个场景题#xff0c;会有点难度#xff01;SQL题该SQL题大量涉及到row_number#xff0c;case when#xff0c;group by等高级用法#xff0c;有一定的实用价值#xff0c;总结出来#xff0c;供日后参考Question.1#xff1a;分组汇总给定筛选条…1个SQL题1个场景题会有点难度SQL题该SQL题大量涉及到row_numbercase whengroup by等高级用法有一定的实用价值总结出来供日后参考Question.1分组汇总给定筛选条件SELECTSales_Month,Customer_ID,AmountFROM(SELECTMONTH(Sales_Date) AS Sales_Month,Customer_ID,sum(Amount) AS AmountFROMSalesGROUP BYSales_Month, Customer_ID) AS AWHEREA.Amount BETWEEN 2000 AND 10000Question.2全集合保留最大值所在行(针对天做处理)为月维度下给定序列号(针对月做处理)Group By Case When 抽取特定值为一个维度SELECTB.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 Item3FROM(SELECTA.Sales_month,A.Customer_ID,A.item,row_number() over (PARTITION BY A.Sales_month, A.Customer_IDORDER BY A.Sales_Date ) AS numsFROM(SELECTconcat(YEAR (Sales_Date), -,MONTH(Sales_Date)) AS Sales_month,Sales_Date,Customer_ID,item,row_number() over (PARTITION BY Sales_Date, Customer_IDORDER BY Amount DESC ) AS numsFROMsales) AS AWHEREA.nums 1) AS BGROUP BYB.Sales_month,B.Customer_IDORDER BYB.Sales_month,B.Customer_IDQuestion.3分别选取两个月的集合对item分类汇总连接集合并计算销售额的差值输出类别并根据差值跟定序号SELECTrow_number() over(ORDER BY A.decrease_num DESC) AS Rank_,A.Item as Item,A.decrease_num AS MoM_DecreaseFROM(selectL.item,(L.Amount-R.Amount) as decrease_numfrom(SELECTitem,sum(Amount) AS AmountFROMsalesWHEREyear(Sales_Date) 2018 and month(Sales_Date)7GROUP BYItem) AS Linner join(SELECTitem,sum(Amount) AS AmountFROMsalesWHEREyear(Sales_Date) 2018 and month(Sales_Date)8GROUP BYItem) AS RON L.itemR.item) AS AORDER BYRank_ ASCLIMIT 10Question.4连续的表示方式8月的每一天相对于7月的某一天以1的方式线性增长排序也是以1的方式线性增长连续情况下两者之间的差值相等对该差值计数即可知道不同的连续天数计算日期排序的序号和日期相对于7月31日的差值针对差值分类汇总计算连续天数和起始日期给出连续天数大于等于3的类别SELECTD.Customer_ID,D.running_days,D.start_date,D.end_dateFROM(SELECTC.Customer_ID,C.diff_value,min( C.Sales_Date ) AS start_date,max( C.Sales_Date ) AS end_date,count( 1 ) AS running_daysFROM(selectB.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_valuefrom(SELECTA.Customer_ID,A.Sales_Date,datediff( A.Sales_Date, 2018-07-31 ) AS day_interval,row_number( ) over(PARTITION BY A.Customer_IDORDER BY A.Sales_Date ) AS day_rankFROM(selectdistinct Sales_Date,Customer_IDfromsales) as AwhereSales_Date2018-08-01and Sales_Date2018-08-31) AS B) as CGROUP BYC.Customer_ID,C.diff_value) as DwhereD.running_days3ORDER BYD.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)DETERMINISTICBEGINDECLARE 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 DOset tmp_dotLOCATE(,,Str);IF tmp_dot0 THENset tmp_dec CAST(SUBSTR(Str,1,tmp_dot-1)AS DECIMAL(8,2));set Str_sumStr_sumif(tmp_dec 100,tmp_dec,0.0);set StrSUBSTR(Str,tmp_dot1,LENGTH(Str)-tmp_dot);set Str_con Str_conif(tmp_dec 100,1,0);ELSEset tmp_dec CAST(Str AS DECIMAL(8,2));set Str_sumStr_sumif(tmp_dec100,tmp_dec,0.0);set Str;set Str_con Str_conif(tmp_dec 100,1,0);END IF;END while;set result IF(Str_con0,ROUND(Str_sum/Str_con,2),0);RETURN result;END$delimiter ;
