窗口函数

OVER

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

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

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比较难调试处理。

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

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;

distinct 与 asc

select distinct a, b，会对 a 和 b 的组合去重。

order by a asc, b desc，会先对 a 升序排，再对 b 降序排。

distinct 只能写一次，而 asc 必须每列都写。

临时表

使用临时表来当数组

WITH used_names AS (
    SELECT 'C' AS cp_name
    UNION ALL SELECT 'D'
), all_names AS (    
    SELECT 'A' AS name
    UNION ALL SELECT name FROM used_names) -- 每多一行，加入一个UNION ALL，各行可以不加 AS name
INSERT OVERWRITE TABLE db.tbl_1
PARTITION (ds = '${last_day}')
SELECT a, b

FROM    db.tbl_2 a
INNER JOIN all_names n
ON      a.cp_name = n.cp_name
LEFT JOIN used_names u
ON      a.cp_name = u.cp_name

在SQL中定义多个CTE（公用表表达式）时，只有第一个CTE前面使用WITH关键字
后续的CTE应该用逗号分隔，而不是每个都使用WITH关键字。

CTE是"Common Table Expression"（公用表表达式）的缩写。它是SQL中的一种临时结果集，可以在SELECT、INSERT、UPDATE或DELETE语句中引用。

基于全量表生成差异天表

全量表每个 ds 都有更新的全量数据。
但是查询的时候用 gmtCreate 只查询天增量。

这样我们就得到了每天新增的新增-天增量表。

join on 可以代替 where in

可以把某些本地的条件移动到另一张表上去，把必须等于用 INNER JOIN b 来表达，把可以等于，但是不必须等于用 left join b 但是 b 的特定列 IS NOT NULL 来表达。

用两个时间段来 union all 来插入 insert overwrite 的时候

union all 的两侧不可时间重叠，不然会插入重复数据。