工作过后才知道的四个SQL秘密结合CASEWHEN将WindowFunction用于其他用途数据仓库？不就是写SQL吗。。。工作中经常用到日期时间段的筛选，因为在拉报表、仪表盘和各种分析的时候，每周、每月、每季度、每年的业绩往往是分析的重点。提取时间段：提取语法--field可以是日、时、分、月、季等--source可以是日期、时间戳类型extract(fieldFROMsource)使用SELECTextract(yearFROM'2020-08-0509:30:08');--结果为2020SELECTextract(quarterFROM'2020-08-0509:30:08');--结果为3SELECTextract(monthFROM'2020-08-0509:30:08');--结果is8SELECTextract(weekFROM'2020-08-0509:30:08');--结果为31，一年中的星期SELECTextract(dayFROM'2020-08-0509:30:08');--结果为5SELECTextract(hourFROM'2020-08-0509:30:08');--结果为9SELECTextract(minuteFROM'2020-08-0509:30:08');--结果为30SELECTextract(secondFROM'2020-08-0509:30:08');--结果为8注意：impala支持：YEAR,QUARTER,MONTH,DAY,HOUR,MINUTE,SECOND,MILLISECOND,EPOCHHive支持：day,dayofweek,hour,minute,month,quarter,二、week和yearHive是从Hive2.2.0开始就引入了这个功能。星期抽取语法在按照星期的间隔进行统计时，需要识别星期一和星期日的日期。这时候经常用到下面的函数：next_day(STRINGstart_date,STRINGday_of_week)--返回当前日期对应的下一周的日期--2020-08-05是星期三SELECTnext_day('2020-08-05','MO')--下周一对应的日期：2020-08-10SELECTnext_day('2020-08-05','TU')--下周二对应的日期：2020-08-1个1SELECTnext_day('2020-08-05','WE')--下周三对应的日期：2020-08-12SELECTnext_day('2020-08-05','TH')--下周三对应的日期Thursday:2020-08-06，也就是这个星期四SELECTnext_day('2020-08-05','FR')--下个星期五对应的日期：2020-08-07，也就是这个星期五SELECTnext_day('2020-08-05','SA')--下一个星期六对应的日期：2020-08-08，也就是这个星期六SELECTnext_day('2020-08-05','SU')--Next对应的日期toSunday:2020-08-09，也就是这个星期天-英文星期一到星期日(Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday)如何获取当前日期是星期几呢日期对应的日期呢周一？只需要获取当前日期下周一对应的日期，再减去7天即可得到：SELECTdate_add(next_day('2020-08-05','MO'),-7);同理，获取当前日期所在周的星期日对应的日期，只需要先获取当前日期的下一个星期一对应的日期，再减去1天得到：selectdate_add(next_day('2020-08-05','MO'),-1)--2020-08-09月份提取语法至于如何从单个日期中提取月份，LAST_DAY函数可以将每个月中的日期更改为该月的最后一天（28日29日、30日或31日），如下：last_day(STRINGdate)使用SELECTLast_day('2020-08-05');--2020-08-31除了上述方法外，还可以使用date_format函数，例如：SELECTdate_format('2020-08-05','yyyy-MM');--2020-08daterangemonthWindow：使用add_months加trunc()application--返回对应的日期之后加减月份--2020-07-05selectadd_months('2020-08-05',-1)--返回当前日期第一个月的日期--2020-08-01selecttrunc("2020-08-05",'MM')从上面的例子可以看出，简单地使用add_months减去N个月只能得到一个整数月份的数据，但是如果加上trunc()函数，它会和之前一样。values从N个月的第一天开始--选择2020-07-05到2020-08-05的所有数据BETWEENadd_months('2020-08-05',-1)AND'2020-08-05'--选择2020-07-01到2020-08-05之间的所有数据BETWEENadd_months(trunc("2020-08-05",'MM'),-1)AND'2020-08-05'这两种方法在日常工作中经常会用到，对于一些复杂的计算任务，为了避免JOIN过多，一些需要抽取的数据通常会在main之前以临时表或者CTE的形式抽取查询块。临时表方法：CREATETEMPORARYTABLEtable_1ASSELECTcolumnsFROMtableA;CREATETEMPORARYtable_2ASSELECTcolumnsFROMtableB;SELECTtable_1.columns,table_2.columns,c.columnsFROMtableCJOINtable_1JOINtable_2;CTE方法：--注意Hive和Impala支持该语法，低版本MySQL--注意（高、高版本支持）HiveImpala支持这种语法，低版本的MySQL不支持(高版本支持)WITHemployee_by_title_countAS(SELECTt.nameasjob_title,COUNT(e.id)asamount_of_employeesFROMemployeeseJOINjob_titlestone.job_title_id=t.idGROUPBY1),salaries_by_titleAS(SELECTnameasjob_title,salaryFROMjob_titles)SELECT*FROMemployee_by_title_counteJOINsalaries_by_titlesONs.job_title=e.job_title可以看到TEMPTABLE和CTEWITH的用法其实很相似，目的都是为了让你的查询更加清晰优雅简洁。很多人习惯把所有的query都写在一个block里，用了太多的JOIN或者SUBQUERY，导致最后失去逻辑，不知道写到哪里去。及时使用TEMPTABLE和CTE作为辅助绝对是非常有帮助的。加分。将聚合函数(SUM/COUNT/COUNTDISTINCT/MIN/MAX)与CASEWHEN结合使用是最强大和有趣的使用方式。这样使用会产生类似EXCEL中SUMIF/COUNTIF的效果，很多高效的分析都可以通过这种方式进行。表名：order列：register_date,order_date,user_id,country,order_sales,order_id数据准备CREATETABLEorder(register_datestring,order_datestring,user_idstring,countrystring,order_salesdecimal(10,2),order_idstring);INSERTINTOTABLEorderVALUES("2020-06-07","2020-06-09","001",'c0',210,"o1");INSERTINTOTABLEorderVALUES("2020-06-08","2020-06-09","002",'c1',220,"o2");INSERTINTOTABLEorderVALUES("2020-06-07","2020-06-10","003",'c2',230,"o3");INSERTINTOTABLEorderVALUES("2020-06-09","2020-06-10","004",'c3',200,"o4");INSERTINTOTABLEorderVALUES("2020-06-07","2020-06-20","005",'c4',300,"o5");INSERTINTOTABLEorderVALUES("2020-06-10","2020-06-23","006",'c5',400,"o6");INSERTINTOTABLEorderVALUES("2020-06-07","2020-06-19","007",'c6',600,"o7");INSERTINTOTABLEorderVALUES("2020-06-12","2020-06-18","008",'c7',700"o8");INSERTINTOTABLEorderVALUES("2020-06-07","2020-06-09","009",'c8',100,"o9");INSERTINTOTABLEorderVALUES("2020-06-15","2020-06-18","0010",'c9',200,"o10");INSERTINTOTABLEorderVALUES("2020-06-15","2020-06-19","0011",'c10',250,"o11");INSERTINTOTABLEorderVALUES("2020-06-12","2020-06-29","0012",'c11',270,"o12");INSERTINTOTABLEorderVALUES("2020-06-16","2020-06-19","0013",'c12',230,"o13");INSERTINTOTABLEorderVALUES("2020-06-17","2020-06-20","0014",'c13',290,"o14");INSERTINTOTABLEorderVALUES("2020-06-20","2020-06-29","0015",'c14',203,"o15");CASEWHEN时间，分析留存率/使用率--允许多列去重sethive.groupby.skewindata=false--允许使用位置编号对sethive进行分组或排序.groupby.orderby.position.alias=trueSELECTdate_add(Next_day(register_date,'MO'),-1)ASweek_end,COUNT(DISTINCTCASEWHENorder_dateBETWEENregister_dateANDdate_add(register_date,6)THENuser_idEND)ASfirst_week_order,COUNT(DISTINCTCASEWHENorder_dateBETWEENdate_add(register_date,7)ANDdate_add(register_date,13)THENuser_idEND)ASsencod_week_order,COUNT(DISTINCTCASEWHENorder_dateBETWEENdate_add(register_date,14)ANDdate_add(register_date,20)THENuser_idEND)astird_week_orderFROMorderGROUPBY1上面的例子可以知道用户注册后是否创建了订单，比如注册后的第一周，第二周分别有多少用户在第一周和第三周下了订单，这样可以分析用户的使用情况和保留情况。注意：以上使用方法需要配置两个参数：hive.groupby.skewindata=false：允许多列去重，否则会报错：SemanticException[Error10022]:DISTINCTondifferentcolumnsnotsupportedwithdatahive.groupby.orderby中的skew.position.alias=true：允许按位置编号分组或排序，否则会报错：SemanticException[Error10025]:line79:13ExpressionnotinGROUPBYkey''MO''当时间时，执行每个用户的消耗量分析selectuser_id，sum（casewhenorder_datebeteinregister_dateandateanddate_add（register_date，6）thenorder_salesend）asfirst_week_amount，sum（casewhenorder_datebet_datebetedebet_datebete_datebetweendate_decker_dequest_eek_date_date_date_date_date_date_date_dateanddocunderter消费的日期，并进行消费金统计，每个用户每个时间段的消费金额（注册后第一周，第二周……等等），可以观察用户是否继续维持消费习惯或消费金额变低等分析。CASEWHEN数量，消费超过一定数量的数量分析SELECTuser_id,COUNT(DISTINCTCASEWHENorder_sales>=100THENorder_idEND)AScount_of_order_greateer_than_100FROMorderGROUPBY1上面的例子类似于countif的用法，对于每个用户，统计订单数量大于一定值的订单数量，并分析以过滤掉高价值客户。CASEWHEN数量，加上时间使用SELECTuser_id,MIN(CASEWHENorder_sales>100THENorder_dateEND)ASfirst_order_date_over1000,MAX(CASEWHENorder_sales>100THENorder_dateEND)ASrecent_order_date_over100FROMorderGROUPBY1CASEWHEN加上整个购买过程中的MIN/MAX时间，可以得到该金额的订单日期，以及购买超过一定金额的最新订单的日期。窗口函数不仅是工作中经常用到的函数，也是面试时经常被问到的问题。一个常见的使用场景是按topN分组。本文介绍的另一种用法是利用窗口函数进行用户访问会话分析。会话是用户在指定时间段内在网站上发生的一系列交互。例如，一个会话可以包含多个页面视图、事件、社交互动和电子商务交易。一个session相当于一个容器，里面包含了用户在网站上的操作。session有一个过期时间，比如30分钟，即不活动超过30分钟，session就会过期。假设张三访问网站，则从他到达网站的那一刻开始计时。如果三十分钟后，张三仍然没有任何形式的互动，则视为本节结束。但是，每当Johnny与某个元素进行交互（例如事件、社交交互或打开新网页）时，该交互的时间将额外增加30分钟，从而重置过期时间。数据准备表名：user_visit_action列：user_id,session_id,page_url,action_timeCREATETABLEuser_visit_action(user_idstring,session_idstring,page_urlstring,action_timestring);INSERTINTOTABLEuser_visit_actionVALUES("001","ss001","http://a.com","2020-08-0613:34:11.478");INSERTINTOTABLEuser_visit_actionVALUES("001","ss001","http://b.com","2020-08-0613:35:11.478");INSERTINTOTABLEuser_visit_actionVALUES("001","ss001""http://c.com","2020-08-0613:36:11.478");INSERTINTOTABLEuser_visit_actionVALUES("001","ss002","http://a.com","2020-08-0614:30:11.478");INSERTINTOTABLEuser_visit_actionVALUES("001","ss002","http://b.com","2020-08-0614:31:11.478");INSERTINTOTABLEuser_visit_actionVALUES("001","ss002","http://e.com","2020-08-0614:33:11.478");INSERTINTOTABLEuser_visit_actionVALUES("001","ss002","http://f.com","2020-08-0614:35:11.478");INSERTINTOTABLEuser_visit_actionVALUES("002","ss003","http://u.com","2020-08-0618:34:11.478");INSERTINTOTABLEuser_visit_actionVALUES("002","ss003","http://k.com","2020-08-0618:38:11.478");用户访问会话分析示例数据表如上，有用户，访问次数，还有页面链接和时间，下面用partitionby表示每个用户的时间在不同的访问。间的浏览为。上面的查询会返回对于每个用户，每次访问，页面浏览行为的顺序，会话的起止时间，基于此，可以将结果存储到TEMPTABLE或CTE中，以供进一步分析。mary本文主要分享四种工作和面试中经常遇到的SQL使用技巧。当然，这些都与具体的分析服务密切相关。最后，不管你是SQLboy还是SQLgirl，只要掌握一些技巧，相信你都能拥有快乐的SQL查询。
