Mysql 场景

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 ;