几道变态的SQL面试题

# 2881 Roman to Integer by SQL

请大家试试用SQL做一下，答案我加密了，有需要的可以看


Table: book_chapters

chapter_name        chapter_number
A Dead Man        LVI
Behaviour in General        I
Cast Up        XLIX
Imitation        IX
Nemesis        L
Paste        XXIII
The Cub        XXI
The Oxenham Arms        XXIV
Two Bequests        XLVII

Output: (用数字排序所有书名 ASC) 问题的核心是在SQL里完成罗马字母和数字的转换，然后才能排序。

chapter_name
Behaviour in General
Imitation
The Cub
Paste
The Oxenham Arms
Two Bequests
Cast Up
Nemesis
A Dead Man

李浩泉 发表于 2020-9-7 08:39
现在让你用SQL写，怎么办？这才是真正DE应该考得，SQL算法，而不是SQL + 算法。

SQL的源程序下面加密了 ...

老哥对DE面试为啥想法有点偏激。。。DE这个东西本来就是方方面面都涉及到的。。。SQL是很重要但并不是所有公司都是SQL heavy的。。。编程，数据模型，product sense，info structure 都是有用到的。。。真的是看公司平时用什么人家考什么的。。。。
亚麻喜欢考SQL是因为他们不同team真正通用的只有红屎。。。不同team会用到不同的工具做data pipeline的。。。
脸熟要考SQL+python也是因为他们data engineer team用到的是脸熟SDE用PYTHON专门给内部开发的类airflow平台。而脸熟的DE是比较少自己开发operator和自己的function的。
骨骼不大熟悉，但是据我所知 骨骼DE极少，大部分data pipeline都是由偏DATA INFO STRUCTURE 的SDE完成的。。剩下的由各种analyst或者BIE拿数据。
我虽然经验不是很多，但是面试过很多公司和很多业内的人也都讨论过，可能是我级别不够，但是面试角度来说考这么极端难度的SQL是其实非常不常见的。。。。
个人浅见 非喜勿喷。

http://sqlfiddle.com/#!17/f65f2/24

1. WITH
   
2.   RomanSymbols AS (
   
3.     SELECT
   
4.       UNNEST(ARRAY['I', 'V', 'X', 'L', 'C', 'D', 'M']) AS roman,
   
5.       UNNEST(ARRAY[1, 5, 10, 50, 100, 500, 1000]) AS number
   
6.   ),
   
7.   BookChaptersWithIds As (
   
8.     SELECT
   
9.       chapter_name, chapter_number,
   
10.       ROW_NUMBER() OVER () AS chapter_id
    
11.     FROM
    
12.       BookChapters
    
13.   ),
    
14.   TranslatedInput AS (
    
15.     SELECT
    
16.       chapter_id, subscript, number
    
17.     FROM
    
18.       (
    
19.         SELECT
    
20.           chapter_id, subscript,
    
21.           SUBSTRING(chapter_number, subscript, 1) AS roman
    
22.         FROM
    
23.           BookChaptersWithIds,
    
24.           GENERATE_SERIES(1, LENGTH(chapter_number)) AS subscript
    
25.       ) t
    
26.       JOIN RomanSymbols s ON t.roman = s.roman
    
27.   ),
    
28.   TranslatedChapterNumbers AS (
    
29.     SELECT
    
30.       l.chapter_id,
    
31.       SUM(CASE WHEN l.number >= COALESCE(r.number, 0)
    
32.                THEN l.number ELSE -l.number
    
33.           END) AS chapter_number
    
34.     FROM
    
35.       TranslatedInput l
    
36.       LEFT JOIN TranslatedInput r ON l.chapter_id = r.chapter_id AND
    
37.                                      l.subscript + 1 = r.subscript
    
38.     GROUP BY l.chapter_id
    
39.   )
    
40. SELECT
    
41.   chapter_name
    
42. FROM
    
43.   BookChaptersWithIds s
    
44.   JOIN TranslatedChapterNumbers t ON s.chapter_id = t.chapter_id
    
45. ORDER BY t.chapter_number;
    

复制代码

现在让你用SQL写，怎么办？这才是真正DE应该考得，SQL算法，而不是SQL + 算法。

SQL的源程序下面加密了，有兴趣的自己先试试看，能不能过这道DE面试题：

用PYTHON，将罗马数字转化为阿拉伯数字，很简单，代码如下：

1. dic = {'I':1,'V':5,'X':10,'L':50,'C':100,'D':500,'M':1000}
   
2.         n = len(s)
   
3.         a = dic[s[n-1]]  
   
4.         
   
5.         while n > 1 :
   
6.             if dic[s[n-2]] >= dic[s[n-1]] :
   
7.                 a = dic[s[n-2]] + a
   
8.                 n -= 1
   
9.             else :
   
10.                 a = a - dic[s[n-2]]
    
11.                 n -= 1
    
12.         return a

复制代码

新建一个table是罗马数字和数字的对应，然后再left join 再order by是不是就行了？

# 1747 - Set weekly alarm_date to trigger the ETL till the end of year （这道简单一些，medium）

alarm_date
------------
2020-09-07
2020-09-14
2020-09-21
2020-09-28
2020-10-05
2020-10-12
2020-10-19
2020-10-26
2020-11-02
2020-11-09
2020-11-16
2020-11-23
2020-11-30
2020-12-07
2020-12-14
2020-12-21
2020-12-28

1. WITH RECURSIVE CTE (alarm_date) AS
   
2. (
   
3.     SELECT CURRENT_DATE AS alarm_date
   
4.     UNION ALL
   
5.     SELECT alarm_date + 7 FROM CTE
   
6.     WHERE EXTRACT(YEAR FROM alarm_date + 7) = EXTRACT(YEAR FROM alarm_date) AND EXTRACT(MONTH FROM alarm_date + 7) <= 12
   
7. )
   
8. 
   
9. SELECT * FROM CTE

复制代码

Vincentw 发表于 2020-9-7 08:57
新建一个table是罗马数字和数字的对应，然后再left join 再order by是不是就行了？

你把我逗乐了，HARD CODE手写吗？

李浩泉 发表于 2020-09-06 18:00:21
你把我逗乐了，HARD CODE手写吗？

CASE WHEN THEN END AS Num

Vincentw 发表于 2020-9-7 09:07
CASE WHEN THEN END AS Num

从1到999，你都要手写吗？写1000个case when？2000怎么办？

magicsets 发表于 2020-9-7 10:53
http://sqlfiddle.com/#!17/f65f2/24

这位高手贡献了PostgreSQL的程序，那我就写个MySQL的给大家参考：
大家自己也试试，看看电面的时候能不能过的了这道SQL题。

1. SELECT
   
2.         chapter_name
   
3.     FROM
   
4.         (SELECT
   
5.              chapter_name,
   
6.              IF(LOCATE('IV',chapter_number) > 0, 4, 0) +
   
7.              IF(LOCATE('IX',chapter_number) > 0, 9, 0) +
   
8.              IF(LOCATE('XL',chapter_number) > 0, 40, 0) +
   
9.              IF(LOCATE('XC',chapter_number) > 0, 90, 0) +
   
10.              IF(LOCATE('CD',chapter_number) > 0, 400, 0) +
    
11.              IF(LOCATE('CM',chapter_number) > 0, 900, 0) as num,
    
12.              REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(chapter_number,
    
13.                  'IV', ''), 'IX', ''), 'XL', ''), 'XC', ''), 'CD', ''), 'CM', '') as chapter_number
    
14.         FROM book_chapters ) e
    
15.         
    
16.     ORDER BY        num +
    
17.         (CHAR_LENGTH(chapter_number) - CHAR_LENGTH(REPLACE(chapter_number, 'I', ''))) * 1 +
    
18.         (CHAR_LENGTH(chapter_number) - CHAR_LENGTH(REPLACE(chapter_number, 'V', ''))) * 5 +
    
19.         (CHAR_LENGTH(chapter_number) - CHAR_LENGTH(REPLACE(chapter_number, 'X', ''))) * 10 +
    
20.         (CHAR_LENGTH(chapter_number) - CHAR_LENGTH(REPLACE(chapter_number, 'L', ''))) * 50 +
    
21.         (CHAR_LENGTH(chapter_number) - CHAR_LENGTH(REPLACE(chapter_number, 'C', ''))) * 100 +
    
22.         (CHAR_LENGTH(chapter_number) - CHAR_LENGTH(REPLACE(chapter_number, 'D', ''))) * 500 +
    
23.         (CHAR_LENGTH(chapter_number) - CHAR_LENGTH(REPLACE(chapter_number, 'M', ''))) * 1000

复制代码