窗口函数

OVER

OVER用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。

在这里要引入窗口函数和开窗函数的概念：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE TABLE Employee
(
ID INT  PRIMARY KEY,
Name VARCHAR(20),
GroupName VARCHAR(20),
Salary INT
)
INSERT INTO  Employee
VALUES(1,'小明','开发部',8000),
      (4,'小张','开发部',7600),
      (5,'小白','开发部',7000),
      (8,'小王','财务部',5000),
      (9, null,'财务部',NULL),
      (15,'小刘','财务部',6000),
      (16,'小高','行政部',4500),
      (18,'小王','行政部',4000),
      (23,'小李','行政部',4500),
      (29,'小吴','行政部',4700);
      
SELECT *,
     SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总工资,
     SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总工资,
     SUM(Salary) OVER(ORDER BY ID) 累计工资,
     SUM(Salary) OVER() 总工资
from Employee
  • SUM(Salary) OVER (PARTITION BY Groupname)
    • 只对PARTITION BY后面的列Groupname进行分组，分组后求解Salary的和。
  • SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)
    • 对PARTITION BY后面的列Groupname进行分组，然后按ORDER BY 后的ID进行排序，然后在组内对Salary进行累加处理。
  • SUM(Salary) OVER (ORDER BY ID)
    • 只对ORDER BY 后的ID内容进行排序，对排完序后的Salary进行累加处理。
  • SUM(Salary) OVER ()
    • 对Salary进行汇总处理

这里的主标题就是开窗函数。

比较容易出错而且难以理解的是：COUNT(DISTINCT wuid) AS unique_wuid_count, SUM(COUNT(DISTINCT wuid)) OVER() AS total_unique_wuid_count from这样的设计，左列的累积和不一定等于右边的值，这样的bug比较难调试处理。

选出通话时间最早的一条记录：

1
2
3
4
5
6
7
select a,l,created_time,row_number() over(partition by a,l order by created_time asc) as rn
                from tmp
                where impl_date=%(i_datefrom)s 
                and to_char(created_time, 'yyyyMMdd') between 
                    %(i_date_pre_m1)s
                    and %(i_datefrom)s) tmp_cri
            where rn=1

case when

嵌套 case when

在select 的一个目标列里：

1
2
-- 在减法以前有一个 case when。在减法外有一个 case when，when 和 then 之间是一个减法
case when ((case when cri.call_time is null then 9999999999 else unix_timestamp(cri.call_time) end)-unix_timestamp(a.created_time)) <= 7 * 24 * 60 * 60 then 1 else 0 end as is_call_in_time

参考：《SQL窗口函数OVER详细用法，一学就会（上）》

缺列的时候mock一列

select “mock_id”, taskid from abc;