关于sql:数仓面试四个在工作后才知道的SQL密技

36次阅读

共计 8646 个字符，预计需要花费 22 分钟才能阅读完成。

SQL 是大数据从业者的必备技能，大部分的大数据技术框架也都提供了 SQL 的解决方案。能够说 SQL 是一种经久不衰、历久弥新的编程语言。尤其是在数仓畛域，应用 SQL 更是粗茶淡饭。本文会分享四个在面试和工作中罕用的几个应用技巧，具体包含：

  • 日期与期间的应用
  • 长期表与 Common Table Expression (WITH)
  • Aggregation 与 CASE WHEN 的联合应用
  • Window Function 的其余用处

数仓？不就是写写 SQL 吗…

第一：日期与期间的应用

日期与时间段的筛选在工作中是常常被用到的，因为在拉取报表、仪表板和各种剖析时，周、月、季度、年度的体现往往是剖析须要考量的重点。

工夫区段的提取：Extract

  • 语法
-- field 能够是 day、hour、minute, month, quarter 等等
-- source 能够是 date、timestamp 类型
extract(field FROM source)
  • 应用
SELECT extract(year FROM '2020-08-05 09:30:08');   -- 后果为 2020
SELECT extract(quarter FROM '2020-08-05 09:30:08');   -- 后果为 3
SELECT extract(month FROM '2020-08-05 09:30:08');   -- 后果为 8
SELECT extract(week FROM '2020-08-05 09:30:08');   -- 后果为 31, 一年中的第几周
SELECT extract(day FROM '2020-08-05 09:30:08');  -- 后果为 5
SELECT extract(hour FROM '2020-08-05 09:30:08');   -- 后果为 9
SELECT extract(minute FROM '2020-08-05 09:30:08');   -- 后果为 30
SELECT extract(second FROM '2020-08-05 09:30:08');   -- 后果为 8

下面可供提取的字段，不同的数据库存在些许的差别。以 Hive 为例，反对 day, dayofweek, hour, minute, month, quarter, second, week 和 year。其中周、月、年应用最为宽泛，因为无论是公司外部产品，还是商用的产品所提供的数据后盾统计，周报和月报(比方近 7 天、近 30 天) 最重视体现的周期。

留神：

impala 反对：YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, MILLISECOND, EPOCH

Hive 反对：day, dayofweek, hour, minute, month, quarter, second, week 和 year

Hive 是从 Hive2.2.0 版本开始引入该函数

周的提取

  • 语法

在依照周的区间进行统计时，须要辨认出周一的日期与周日的日期，这个时候常常会用到上面的函数：

next_day(STRING start_date, STRING day_of_week)
-- 返回以后日期对应的下一个周几对应的日期
-- 2020-08-05 为周三
SELECT next_day('2020-08-05','MO') -- 下一个周一对应的日期：2020-08-10
SELECT next_day('2020-08-05','TU') -- 下一个周二对应的日期：2020-08-11
SELECT next_day('2020-08-05','WE') -- 下一个周三对应的日期：2020-08-12
SELECT next_day('2020-08-05','TH') -- 下一个周四对应的日期：2020-08-06，即为本周四
SELECT next_day('2020-08-05','FR') -- 下一个周五对应的日期：2020-08-07，即为本周五
SELECT next_day('2020-08-05','SA') -- 下一个周六对应的日期：2020-08-08，即为本周六
SELECT next_day('2020-08-05','SU') -- 下一个周日对应的日期：2020-08-09，即为本周日
-- 星期一到星期日的英文（Monday，Tuesday、Wednesday、Thursday、Friday、Saturday、Sunday）
  • 应用

那么该如何获取以后日期所在周的周一对应的日期呢？只须要先获取以后日期的下周一对应的日期，而后减去 7 天，即可取得：

SELECT date_add(next_day('2020-08-05','MO'),-7);

同理，获取以后日期所在周的周日对应的日期，只须要先获取以后日期的下周一对应的日期，而后减去 1 天，即可取得：

select date_add(next_day('2020-08-05','MO'),-1) 
-- 2020-08-09

月的提取

  • 语法

至于怎么将月份从繁多日期提取进去呢，LAST_DAY 这个函数能够将每个月中的日期变成该月的最初一天(28 号，29 号，30 号或 31 号)，如下：

last_day(STRING date)
  • 应用
SELECT last_day('2020-08-05'); -- 2020-08-31

除了下面的形式，也能够应用 date_format 函数，比方：

SELECT date_format('2020-08-05','yyyy-MM');
-- 2020-08

日期的范畴

月的 Window：应用 add_months 加上 trunc()的利用

-- 返回加减月份之后对应的日期
-- 2020-07-05
select add_months('2020-08-05', -1)

-- 返回以后日期的月初日期
-- 2020-08-01
select trunc("2020-08-05",'MM')

由下面范例可见，单纯应用 add_months，减 N 个月的用法，能够刚好取到整数月的数据，但如果加上 trunc()函数，则会从前 N 个月的一号开始取值。

-- 选取 2020-07-05 到 2020-08-05 所有数据
BETWEEN add_months('2020-08-05', -1) AND '2020-08-05' 
-- 选取 2020-07-01 到 2020-08-05 之间所有数据
BETWEEN add_months(trunc("2020-08-05",'MM'),-1) AND '2020-08-05'

第二：长期表与 Common Table Expression (WITH)

这两种办法是日常工作中常常被应用到，对于一些比较复杂的计算工作，为了防止过多的 JOIN，通常会先把一些须要提取的局部数据应用长期表或是 CTE 的模式在次要查问区块前进行提取。

长期表的作法：

CREATE TEMPORARY TABLE table_1 AS  
    SELECT 
        columns
    FROM table A;
CREATE TEMPORARY table_2 AS 
    SELECT
        columns
    FROM table B;

SELECT
    table_1.columns,
    table_2.columns, 
    c.columns 
FROM table C JOIN table_1
     JOIN table_2;

CTE 的作法：

-- 留神 Hive、Impala 反对这种语法，低版本的 MySQL 不反对(高版本反对)
WITH employee_by_title_count AS (
    SELECT
        t.name as job_title
        , COUNT(e.id) as amount_of_employees
    FROM employees e
        JOIN job_titles t on e.job_title_id = t.id
    GROUP BY 1
),
salaries_by_title AS (
     SELECT
         name as job_title
         , salary
     FROM job_titles
)
SELECT *
FROM employee_by_title_count e
    JOIN salaries_by_title s ON s.job_title = e.job_title

能够看到 TEMP TABLE 和 CTE WITH 的用法其实十分相似，目标都是为了让你的 Query 更加高深莫测且优雅简洁。很多人习惯将所有的 Query 写在繁多的区块外面，用过多的 JOIN 或 SUBQUERY，导致最初逻辑失落且本人也搞不清楚写到哪里，适时的应用 TEMP TABLE 和 CTE 作为辅助，相对是很加分的。

第三：Aggregation 与 CASE WHEN 的联合应用

将 Aggregation function (SUM/COUNT/COUNT DISTINCT/MIN/MAX) 联合 CASE WHEN 是最弱小且最乏味的应用形式。这样的应用发明出一种相似 EXCEL 中 SUMIF/COUNTIF 的成果，能够用这个形式做出很多高效的剖析。

  • Table Name: order
  • Column: register_date, order_date, user_id, country, order_sales, order_id

数据筹备

CREATE TABLE order(
    register_date string,
    order_date string,
    user_id string,
    country string,
    order_sales decimal(10,2),
    order_id string);

INSERT INTO TABLE order VALUES("2020-06-07","2020-06-09","001",'c0',210,"o1");
INSERT INTO TABLE order VALUES("2020-06-08","2020-06-09","002",'c1',220,"o2");
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-10","003",'c2',230,"o3");
INSERT INTO TABLE order VALUES("2020-06-09","2020-06-10","004",'c3',200,"o4");
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-20","005",'c4',300,"o5");
INSERT INTO TABLE order VALUES("2020-06-10","2020-06-23","006",'c5',400,"o6");
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-19","007",'c6',600,"o7");
INSERT INTO TABLE order VALUES("2020-06-12","2020-06-18","008",'c7',700,"o8");
INSERT INTO TABLE order VALUES("2020-06-07","2020-06-09","009",'c8',100,"o9");
INSERT INTO TABLE order VALUES("2020-06-15","2020-06-18","0010",'c9',200,"o10");
INSERT INTO TABLE order VALUES("2020-06-15","2020-06-19","0011",'c10',250,"o11");
INSERT INTO TABLE order VALUES("2020-06-12","2020-06-29","0012",'c11',270,"o12");
INSERT INTO TABLE order VALUES("2020-06-16","2020-06-19","0013",'c12',230,"o13");
INSERT INTO TABLE order VALUES("2020-06-17","2020-06-20","0014",'c13',290,"o14");
INSERT INTO TABLE order VALUES("2020-06-20","2020-06-29","0015",'c14',203,"o15");

CASE WHEN 工夫，进行留存率 / 使用率的剖析

-- 容许多列去重
set hive.groupby.skewindata = false
-- 容许应用地位编号分组或排序
set hive.groupby.orderby.position.alias = true

SELECT
    date_add(Next_day(register_date, 'MO'),-1) AS week_end,
    COUNT(DISTINCT CASE WHEN order_date BETWEEN register_date AND date_add(register_date,6) THEN user_id END) AS first_week_order,
    COUNT(DISTINCT CASE WHEN order_date BETWEEN date_add(register_date ,7) AND date_add(register_date,13) THEN user_id END) AS sencod_week_order,
    COUNT(DISTINCT CASE WHEN order_date BETWEEN date_add(register_date ,14) AND date_add(register_date,20) THEN user_id END) as third_week_order
FROM order
GROUP BY 1

下面的示例能够得悉到用户在注册之后，有没有创立订单的行为。比方注册后的第一周，第二周，第三周别离有多少下单用户，这样能够剖析出用户的应用状况和留存状况。

留神：下面的应用形式，须要配置两个参数：

hive.groupby.skewindata = false：容许多列去重，否则报错：

SemanticException [Error 10022]: DISTINCT on different columns not supported with skew in data

hive.groupby.orderby.position.alias = true：容许应用地位编号分组或排序, 否则报错：

SemanticException [Error 10025]: line 79:13 Expression not in GROUP BY key ''MO''

CASE WHEN 工夫，进行每个用户生产金额的剖析

SELECT
    user_id,
    SUM (CASE WHEN order_date BETWEEN register_date AND date_add(register_date,6) THEN order_sales END) AS first_week_amount,
    SUM (CASE WHEN order_date BETWEEN date_add(register_date ,7) AND date_add(register_date,13) THEN order_sales END) AS second_week_amount
    FROM order
GROUP BY 1

通过筛选出注册与生产的日期，并且进行生产金额统计，每个用户在每段时间段 (注册后第一周、第二周…以此类推) 的生产金额，能够察看用户是否有继续维持生产习惯或是生产金额变低等剖析。

CASE WHEN 数量，生产金额超过某一定额的数量剖析

SELECT
    user_id,
    COUNT(DISTINCT CASE WHEN order_sales >= 100 THEN order_id END) AS count_of_order_greateer_than_100
FROM order
GROUP BY 1

下面的示例就是相似 countif 的用法，针对每个用户，统计其订单金额大于某个值的订单数量，剖析去筛选出高价值的顾客。

CASE WHEN 数量，加上工夫的用法

SELECT
    user_id,
    MIN(CASE WHEN order_sales > 100 THEN order_date END) AS first_order_date_over1000,
    MAX(CASE WHEN order_sales > 100 THEN order_date END) AS recent_order_date_over100
FROM order
GROUP BY 1

CASE WHEN 加上 MIN/MAX 工夫，能够得出该用户在其整个应用过程中，首次购买超过肯定金额的订单日期，以及最近一次购买超过肯定金额的订单日期。

第四：Window Function 的其余用处

Window Function 既是工作中常常应用的函数，也是面试时常常被问到的问题。常见的应用场景是分组取 topN。本文介绍的另外一个用法，应用开窗函数进行用户拜访 session 剖析。

session 是指在指定的时间段内用户在网站上产生的一系列互动。例如，一次 session 能够蕴含多个网页浏览、事件、社交互动和电子商务交易。session 就相当于一个容器，其中蕴含了用户在网站上执行的操作。

session 具备一个过期工夫，比方 30 分钟，即不活动状态超过 30 分钟，该 session 就会过期。

假如张三拜访了网站，从他达到网站的那一刻开始，就开始计时。如果过了 30 分钟，而张三依然没有进行任何模式的互动，则视为本次 session 完结。然而，只有张三与某个元素进行了互动（例如产生了某个事件、社交互动或关上了新网页），就会在该次互动的工夫根底上再减少 30 分钟，从而重置过期工夫。

数据筹备

  • Table Name: user_visit_action
  • Columns: user_id, session_id , page_url, action_time
CREATE TABLE user_visit_action( 
    user_id string,
    session_id string,
    page_url string,
    action_time string);
    
INSERT INTO TABLE user_visit_action VALUES("001","ss001","http://a.com","2020-08-06 13:34:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss001","http://b.com","2020-08-06 13:35:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss001","http://c.com","2020-08-06 13:36:11.478");

INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://a.com","2020-08-06 14:30:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://b.com","2020-08-06 14:31:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://e.com","2020-08-06 14:33:11.478");
INSERT INTO TABLE user_visit_action VALUES("001","ss002","http://f.com","2020-08-06 14:35:11.478");

INSERT INTO TABLE user_visit_action VALUES("002","ss003","http://u.com","2020-08-06 18:34:11.478");
INSERT INTO TABLE user_visit_action VALUES("002","ss003","http://k.com","2020-08-06 18:38:11.478");

用户拜访 session 剖析

范例的材料表如上，有使用者、访次和页面的连结和工夫。以下则应用 partition by 来表白每个使用者在不同访次之间的浏览行为。

SELECT
    user_id,
    session_id,
    page_url,
    DENSE_RANK() OVER (PARTITION BY user_id, session_id ORDER BY action_time ASC) AS page_order,
    MIN(action_time) OVER (PARTITION BY user_id, session_id) AS session_start_time,
    MAX(action_time) OVER (PARTITION BY user_id, session_id) AS session_finisht_time
FROM user_visit_action

下面的查问会返回针对每个用户、每次的到访，浏览页面行为的先后秩序，以及该 session 开始与完结的工夫，以此为根底就能够将这个后果存入 TEMP TABLE 或是 CTE，进行更进一步的剖析。

小结

本文次要分享了四个在工作和面试中常常遇到的 SQL 应用技巧。当然，这些都与具体的剖析业务非亲非故。最初，不论你是 SQL boy or SQL girl，只有是把握一些技巧，置信都可能 Happy SQL querying ????。

公众号『大数据技术与数仓』，回复『材料』支付大数据资料包

正文完
sql
发表至： sql
2020-08-07
 0
关于sql:精彩联动OpenMLDB-Pulsar-Connector原理和实操
关于sql:自建Hive数据仓库跨版本迁移到阿里云Databricks数据洞察
关于sql:SQL审核-这里有-MySQLOracle-最常用的-SQL-开发规则
关于sql:知识扩展SQL查询基础