数仓中几个SQL技巧

最新推荐文章于 2022-08-23 22:20:58 发布

原创

最新推荐文章于 2022-08-23 22:20:58 发布 · 415 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#hive #大数据

本文介绍了数仓中SQL的四个实用技巧：日期与期间操作，临时表与Common Table Expression，Aggregation与CASE WHEN结合，以及Window Function在用户访问session分析中的应用。详细阐述了如何利用这些技巧进行数据筛选、留存率分析、消费金额统计及session时间计算，提升数据分析效率。

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，Tu