数据分析常用sql

1、Mysql

      在sql 语句中的执行顺序是 from 、where 、order by、select 

     coalesce(), with rollup

select coalesce(t.name,'总'),sum(t.v) from (select '小丽' as name,1 as v UNION ALL select '小丽' as name,2 as v ) t group by t.name with ROLLUP

    分组top N  窗口函数里不能用聚合函数

　　本篇介绍的三个函数均MySQL 8.x 以上版本，8.x以下版本会报语法错误，属于正常现象。MySQL 8.x 实际上就是 MySQL 5.8x，大概是为了通过更大版本型号数字，证明自己比友商先进吧。

      字段解释：D2  商品分类,gmv,g_name 商品名称  

     需求：求每个商品分类 gmv 销售前N

    步骤一：  计算汇总商品 GMV

             

SELECT sum( xgmv ) gmv, g_name, D2 FROM tb_tmp_name GROUP BY D2, g_name 

步骤二：

        rank() over (parition by D2 order by GMV DESC )   rn 组内排序

步骤二：

        rank() over (parition by D2 order by GMV DESC )   rn 组内排序

              

 SELECT
        t.*,
        rank() over ( PARTITION BY D2 ORDER BY gmv DESC ) rn 
    FROM
        ( SELECT sum( xgmv ) gmv, g_name, D2 FROM tb_tmp_name GROUP BY D2, g_name ) t 

步骤三：

        rank() over (order by GMV DESC )   rn 整组排序，没有Parition by 分组 那就默认一个数据组

             

       整理如下： 

SELECT
    *,
    ROW_NUMBER() over ( ORDER BY gmv DESC ) rnn 
FROM
    (
    SELECT
        t.*,
        rank() over ( PARTITION BY D2 ORDER BY gmv DESC ) rn 
    FROM
        ( SELECT sum( xgmv ) gmv, g_name, D2 FROM tb_tmp_name GROUP BY D2, g_name ) t 
    ) n 
WHERE
    rn < 3 
ORDER BY
    gmv DESC

   最后效果如下：

 

 1.2 

       

     

RANK() OVER(业务逻辑)

作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。

说明：例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。即：1 1 3 4 5 5 7

DENSE_RANK() OVER(业务逻辑) 

作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。

说明：和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次。即：1 1 2 3 4 5 5 6

ROW_NUMBER() OVER(业务逻辑)

作用：查出指定条件后的进行排名，条件相同排名也不相同，排名间断不连续。

说明：这个函数不需要考虑是否并列，即使根据条件查询出来的数值相同也会进行连续排序。即：1 2 3 4 5 6

 if 排序：

     

select  
        first_name,
        gender,
        age,
        @rank:=if(@gen=gender,@rank+1,1) rank,
        @gen:=gender
    from person,(select @rank:=0,@gen:=null) temp
    order by gender, age asc

   

         来自知乎：

　　   SQL高级功能：窗口函数、存储过程及经典排名问题、topN问题等 - 光or彗的文章 - 知乎 https://zhuanlan.zhihu.com/p/328504991

1.3 

　　　　窗口函数已经具备了前几节中group by和order by子句的分组和排序的功能，但仍要用窗口函数是因为，group by分组汇总后改变了表的行数，一行只有一个类别，而partition by和rank函数不会减少原表中的行数。

                “窗口函数”之所以叫“窗口”函数，是因为partition by分组后的结果就称为“窗口”,这里的窗口是表示“范围”的意思

                    

             窗口函数的用处是：聚合函数作为窗口函数，可以在每一行的数据里直观看到，截止到本行数据，统计数据有多少，最大值、最小值是多少等，从而可以看出每一行数据，对整体数据的影响

2、Presto