当前位置:主页 > 产品展示 > 实验室耗材 >

产品展示

Products Classification

数仓面试|四个在事情后才知道的SQL密技

  • 产品时间:2021-11-22 01:02
  • 价       格:

简要描述:SQL是大数据从业者的必备技术,大部门的大数据技术框架也都提供了SQL的解决方案。可以说SQL是一种耐久不衰、耐久弥新的编程语言。 尤其是在数仓领域,使用SQL更是屡见不鲜。本文会分享四个在面试和事情中常用的几个使用技巧,详细包罗:日期与期间的使用暂时表与Common Table Expression (WITH)Aggregation 与CASE WHEN的联合使用Window Function的其他用途数仓?...

详细介绍
本文摘要:SQL是大数据从业者的必备技术,大部门的大数据技术框架也都提供了SQL的解决方案。可以说SQL是一种耐久不衰、耐久弥新的编程语言。 尤其是在数仓领域,使用SQL更是屡见不鲜。本文会分享四个在面试和事情中常用的几个使用技巧,详细包罗:日期与期间的使用暂时表与Common Table Expression (WITH)Aggregation 与CASE WHEN的联合使用Window Function的其他用途数仓?

米乐体育app在线

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'); -- 效果为 2020SELECT extract(quarter FROM '2020-08-05 09:30:08'); -- 效果为 3SELECT extract(month FROM '2020-08-05 09:30:08'); -- 效果为 8SELECT extract(week FROM '2020-08-05 09:30:08'); -- 效果为 31,一年中的第几周SELECT extract(day FROM '2020-08-05 09:30:08'); -- 效果为 5SELECT extract(hour FROM '2020-08-05 09:30:08'); -- 效果为 9SELECT extract(minute FROM '2020-08-05 09:30:08'); -- 效果为 30SELECT 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, EPOCHHive支持:day, dayofweek, hour, minute, month, quarter, second, week 和 yearHive是从Hive2.2.0版本开始引入该函数周的提取语法在根据周的区间举行统计时,需要识别出周一的日期与周日的日期,这个时候经常会用到下面的函数:next_day(STRING start_date, STRING day_of_week)-- 返回当前日期对应的下一个周几对应的日期-- 2020-08-05为周三SELECT next_day('2020-08-05','MO') -- 下一个周一对应的日期:2020-08-10SELECT next_day('2020-08-05','TU') -- 下一个周二对应的日期:2020-08-11SELECT next_day('2020-08-05','WE') -- 下一个周三对应的日期:2020-08-12SELECT 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-05select add_months('2020-08-05', -1)-- 返回当前日期的月初日期-- 2020-08-01select 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: orderColumn: 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 = trueSELECT 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_orderFROM orderGROUP BY 1上面的示例可以得知到用户在注册之后,有没有建立订单的行为。

好比注册后的第一周,第二周,第三周划分有几多下单用户,这样可以分析出用户的使用情况和留存情况。注意:上面的使用方式,需要设置两个参数:hive.groupby.skewindata = false:允许多列去重,否则报错:SemanticException [Error 10022]: DISTINCT on different columns not supported with skew in datahive.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 orderGROUP BY 1通过筛选出注册与消费的日期,而且举行消费金额统计,每个用户在每段时间段(注册后第一周、第二周…以此类推)的消费金额,可以视察用户是否有连续维持消费习惯或是消费金额变低平分析。CASE WHEN数量,消费金额凌驾某一定额的数量分析SELECT user_id, COUNT(DISTINCT CASE WHEN order_sales >= 100 THEN order_id END) AS count_of_order_greateer_than_100FROM orderGROUP 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_over100FROM orderGROUP BY 1CASE WHEN加上MIN/MAX时间,可以得出该用户在其整个使用历程中,首次购置凌驾一定金额的订单日期,以及最近一次购置凌驾一定金额的订单日期。

第四:Window Function的其他用途Window Function既是事情中经常使用的函数,也是面试时经常被问到的问题。常见的使用场景是分组取topN。本文先容的另外一个用法,使用开窗函数举行用户会见session分析。session是指在指定的时间段内用户在网站上发生的一系列互动。

例如,一次session可以包罗多个网页浏览、事件、社交互动和电子商务生意业务。session就相当于一个容器,其中包罗了用户在网站上执行的操作。session具有一个逾期时间,好比30分钟,即不运动状态凌驾 30 分钟,该session就会过时。假设张三会见了网站,从他到达网站的那一刻开始,就开始计时。

米乐体育app在线

如果过了 30 分钟,而张三仍然没有举行任何形式的互动,则视为本次session竣事。可是,只要张三与某个元素举行了互动(例如发生了某个事件、社交互动或打开了新网页),就会在该次互动的时间基础上再增加 30 分钟,从而重置逾期时间。数据准备Table Name: user_visit_actionColumns: user_id, session_id , page_url, action_timeCREATE 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_timeFROM user_visit_action上面的查询会返回针对每个用户、每次的到访,浏览页面行为的先后序次,以及该session开始与竣事的时间,以此为基础就可以将这个效果存入TEMP TABLE或是CTE ,举行更进一步的分析。小结本文主要分享了四个在事情和面试中经常遇到的SQL使用技巧。

固然,这些都与详细的分析业务息息相关。最后,不管你是SQL boy or SQL girl,只要是掌握一些技巧,相信都能够Happy SQL querying。> 民众号『大数据技术与数仓』,回复『资料』领取大数据资料包。


本文关键词:米乐体育app在线,数仓,面试,四个,在,事情,后才,知道,的,SQL,SQL

本文来源:米乐体育app在线-www.sh-smithweixiu.com

 


产品咨询

留言框

  • 产品:

  • 留言内容:

  • 您的单位:

  • 您的姓名:

  • 联系电话:

  • 常用邮箱:

  • 详细地址:


推荐产品

Copyright © 2007-2021 www.sh-smithweixiu.com. 米乐体育app在线科技 版权所有 备案号:ICP备96791759号-9

在线客服 联系方式 二维码

服务热线

015-84985793

扫一扫,关注我们