請教一道SQL題

colospring 发表于 2020-8-20 15:11
where (event_type, date) in (select event_type, max(date) from table group by ev ...

给出正确答案也没人理，加个米安慰一下吧！

这个？
select event_type, num from (
  select event_type,
      date,
      num,
      row_number() over (partition by event_type order by date desc) as date_rank
  from table
) where date_rank=1

PostgreSQL / MySQL 特有的解法：（2颗星，考察你是否了解 PostgreSQL / MySQL 和 SQL Server 的区别，实际ETL中不推荐，执行效率很低）

1. SELECT Event_type,
   
2.        Date,
   
3.        Num
   
4. FROM EVENT
   
5. WHERE (Event_type,Date) IN
   
6.                       ( SELECT Event_type,MAX(Date)
   
7.                         FROM EVENT
   
8.                         GROUP BY Event_type )

复制代码

SQL Server 传统解法一：（3颗星）

1. SELECT Event_Type,
   
2.        Date,
   
3.        Num
   
4. FROM (
   
5.     SELECT Event_Type,
   
6.            Date,
   
7.            Num,
   
8.            DENSE_RANK() OVER(PARTITION BY Event_Type ORDER BY Date DESC) AS RK
   
9.     FROM Event) T
   
10. WHERE RK = 1

复制代码

SQL Server 传统解法二：（3.5颗星）

1. WITH CTE AS
   
2. (
   
3. SELECT Event_Type,MAX(Date) AS DATE FROM Event GROUP BY Event_Type
   
4. )
   
5. 
   
6. SELECT E.Event_Type,
   
7.        E.Date,
   
8.        E.Num
   
9. FROM Event E INNER JOIN CTE ON E.Event_Type = CTE.Event_Type AND E.Date = CTE.DATE
   
10. ORDER BY 1 ASC

复制代码

SQL Server 传统解法三：（4颗星）

1. WITH CTE AS
   
2. (
   
3. SELECT Event_Type,MAX(Date) AS DATE FROM Event GROUP BY Event_Type
   
4. )
   
5. 
   
6. SELECT E.Event_Type,
   
7.        E.Date,
   
8.        E.Num
   
9. FROM Event E,CTE
   
10. WHERE E.Event_Type = CTE.Event_Type AND E.Date = CTE.DATE
    
11. ORDER BY 1 ASC

复制代码

SQL Server 传统解法四：（2.5颗星）

1. SELECT Event_type,
   
2.        Date,
   
3.        Num
   
4. FROM EVENT E1
   
5. WHERE Date = (SELECT MAX(Date)
   
6.                         FROM EVENT E2
   
7.                         WHERE E1.Event_type = E2.Event_type)
   
8. ORDER BY 1 ASC

复制代码

关于SQL的效率：

1. RANK（），这些WINDOWS FUNCTION效率很低，尽量用MIN（），MAX（）解决；
2. UNION，少用，用UNION ALL，然后再SELECT DISTINCT
3. LAG（），这类的就更要少用，甚至不用
4. JOIN ON 后面的条件尽量简单，JOIN ON 的效率低于 WHERE
5. WHERE 后面直接加条件，尽量不要在 WHERE 里面用 in line query，比如：WHERE ID NOT IN （SELECT .....  FROM .......）

SQL ETL Developer/BIE/DE：赛车手，驾驶汽车参加比赛

SDE：汽车工程师，设计制造汽

论坛上活跃的ID，SDE居多，又以亚麻的SDE最多，毕竟亚麻是60万人的特大厂。术业各有专攻，人人都会开车，但不是人人都是舒马赫。有一些SDE经常鄙视BIE/DE，认为SQL不是技术。其实，如果你真的让一个SDE去干BIE或者DE的工作，大部分时候，可能真做不好。这就相当于，你让宝马的顶级机修工程师去参加F1当赛车手，结果可想而知。会一点点MySQL或者PostgreSQL就也出来面试了。遇见一个6-7年的ORACLE高手，反而觉得ORACLE高手是傻13，反正我面试你，我怎么说都对，你敢反驳，你就是LP不合格，冷冻2年。呵呵呵。

SELECT *
FROM ...
WHERE (ID,DATE) IN   ( SELECT ID, MAX(DATE)
                                      FROM ...
                                      WHERE
                                      GROUP BY  )

这是COST最高的SQL算法，相当于

FOR A,B IN enumerate(X):
    FOR C,D IN enumerate(Y):
        IF A == C AND B == D:

无知者无畏，如果面试官倾向和引导你去这么做，只能说这个面试官根本不懂SQL，没系统学过ORACLE和SQL Server 基础不牢，从未有过大型数据仓库开发经验。自己就没写过ETL SSIS package。就像一些只会for loop就自称也会编程一样。

写SELECT FROM很简单，但不是说会SELECT FROM就懂SQL
写FOR LOOP很简单，但不是说会FOR LOOP就懂CODING

会SQL的人从未鄙视过CODING，也请CODING的人尊重写SQL的同事

李浩泉 发表于 2020-08-20 22:15:16
PostgreSQL / MySQL 特有的解法：（2颗星，考察你是否了解 PostgreSQL / MySQL 和 SQL Server 的区别，实际ETL中不推荐，执行效率很低）

SELEC

謝謝大神。尤其四顆星的解答我自己應該想不到。

其实不用window function啦 把subquery放在where感觉效率蛮低的 放在from里让sql engine去handle这些join会快很多也减少很多重复运算:

1. SELECT
   
2.     e.event_type,
   
3.     e.date,
   
4.     e.num
   
5. FROM
   
6.     event AS e,
   
7.     (   SELECT
   
8.             e2.event_type,
   
9.             MAX(e2.date) AS latest_date
   
10.         FROM event AS e2
    
11.         GROUP BY e2.event_type
    
12.     ) event_type_to_latest_date
    
13. WHERE e.event_type = event_type_to_latest_date.event_type
    
14.     AND e.date = event_type_to_latest_date.latest_date
    
15. ;
    

复制代码

考虑duplicates和无gap的话就用Dense_rank, 不然就是row number了。楼主可能说的是window function，那样的话也挺快的

李浩泉 发表于 2020-8-21 13:25
关于SQL的效率：

1. RANK（），这些WINDOWS FUNCTION效率很低，尽量用MIN（），MAX（）解决；

window function 效率不总是最低的。当题目的解答只有window function和self join两个选择的时候，需要self join的table本身又很大，window function 反而是条理清晰又高效的解法