1 动态时间变量
动态时间变量:
where aa.dt = ‘${#date(0,0,-1):yyyy-MM-dd#}’
yyyy-MM-dd是起始日期,会默认为今天的日期,也可以将其中的一部分设置为固定的格式,例如:yyy-MM-05设置为固定的当前年的当前月的5号。
date(0,0,-1),为日期变化,此处表示将dd减一天。
我的错误:
- 不能换成#:where aa.dt = #{#date(0,0,-1):yyyy-MM-dd#}
- 不能用mm:where aa.dt = ‘${#date(0,0,-1):yyyy-mm-dd#}’
2 SQL中的时间格式字符串
参考文献:Java SQL中时间的格式化总结
SQL中的时间格式,例如:
yyyy-MM-dd 表示 : 年-月-日
yyyy-MM-dd HH:mm:ss 表示 : 年-月-日 时:分:秒
(1)单个(字母)和成对(字母)的区别
在通常情况下M、D、H、m、s都是成对出现的(MM,dd,HH,mm,ss)
但是有时也会看到它们单独存在(M,d,H,m,s)。
它们的区别为是否有前导零:M,d,H,m,s表示非零开始,MM,dd,HH,mm,ss表示从零开始。
举例:
yyyy-M-d H: m:s ---------------- 2019-2-9 1:2:5
yyyy-MM-dd HH:mm:ss ---------------- 2019-02-09 01:02:05
(2)大写(字母)和小写(字母)的区别
- Y与y的区别
官方解释:
.
“YYYY format” 是 “ISO week numbering system”
“yyyy format” 是 “Gregorian
Calendar” “YYYY specifies the week of the year (ISO) while yyyy
specifies the calendar year (Gregorian)"
.
意思就是yyyy为Year,YYYY为 Week Year。
总之,正常情况下如果希望按照日历格式得到每天的日期字符的话,使用 “yyyy”。 - M与m的区别
因为月(Month)和 分(Minute)首字母一样的缘故,在Java中用 大写的 “M”表示月,用小写的“m”表示分钟。
又因为在SQL中不区分大小写的缘故、“MM” 或 “mm” 表示月,“MI” 或 “mi” 表示分钟。
注意:如“1动态时间变量”所示,在hive中执行KaTeX parse error: Expected '}', got '#' at position 2: {#̲date(0,0,0):yyy…{#date(0,0,0):yyyy-mm-dd#}效果不同,用在where中,后者会少很多数据。 - H与h的区别(24小时制和12小时制的)
在Java中大写的 “HH” 表示 24 小时制时间格式,小写的 “hh” 表示12小时制的时间格式。
又因为在SQL中不区分大小写的缘故 ,“HH24” 或 “hh24” 表示 24 小时制时间格式,“HH” 或 “hh” 默认为 12 小时制时间格式。
3 substr与substring
只写一个参数没区别,就是该参数的位置到字符串的最后。
var a="abcdefghiklmnopqrstuvwxyz";
var b=a.substr(3,5);
var c=a.substring(3,5);
打印输出的结果是:
defgh
de
注意最后5下标是不会取到的意思是只能截取a字符串的3,4下标
截取的时候是不会截取到最后一个[3,5)
4 order by / sort by /distribute by / cluster by
5 in 与exists
参考文献:SQL中in和exists的区别
一句话总结:in先执行子查询,再执行主查询;而exists先执行主查询,再执行子查询。
具体的:
- in在查询的时候,首先查询子查询的表,然后将内表和外表做一个笛卡尔积,然后按照条件进行筛选。
- exists先执行主查询,再根据主查询的结果,执行子查询。
具体例子,见参考文献。
应用场景:
当子查询的结果较小,且主查询的表较大且有索引时,应用in。
当主查询的结果较小,而子查询的表较大且有索引时,应用exists。
即先把大表减小,然后再去匹配另一个表。
in和exists的不同,即驱动顺序的不同(这是性能变化的关键)。如果是exists,那么以外层表为驱动表,先被访问。如果是IN,那么先执行子查询,以内层表为驱动表。
所以我们以驱动表的快速返回为目的(即越快获得驱动表,越好),那么就会考虑到索引及结果集的关系了 。另外IN时不对NULL进行处理。
in 是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
6 开窗函数
6.1 开窗函数与聚合函数的异同
- 相同点:先对数据进行分组,再对每个分组内的数据进行处理。所以说两种函数其实是同类函数。
- 不同点:对于每个组,聚合函数只能返回一行,而开窗函数可以返回多行。
可参考:
(1) 开窗函数与聚合函数的区别-博客园
(2) Oracle分析函数与聚合函数的区别-CSDN-例子清晰
6.2 开窗函数介绍
格式:函数f over()
- **over()**称为窗口函数,该函数定义了窗口的内容。
- 函数f,即开窗函数(开窗,可以理解为 函数f 打开 窗口over())。
6.2.1 窗口函数over
语法:over(partition by column1 order by column2)
。
功能:先根据字段column1分组(也可以称为分区),再根据字段column2排序。
什么是窗口?
窗口就是一个字段的数据范围。如下图,月份字段只有两个不同的值,所以只能分成两个窗口。可以用语句 over(partition by 月份 order by 门店)实现,即按月份分组,按门店排序。
6.2.3 开窗函数
开窗函数并不是窗口函数over专用的函数,而是这类函数因为和over一起使用,所以称为开窗函数,常用的例如: sum 、avg、count、first_value、last_value、lag、lead、row_number、dense_rank、rank。
这些函数可以分为三类,这些函数都作用于单个窗口:
(1)聚合函数变为窗口函数: sum 、avg、count。
即对每个窗口求和、求平均、计数。
(2)取首尾值、当前行附近的数据:first_value、last_value、lag、lead。
first_value:是在窗口里面取到第一个值。
last_value:是在窗口里面取到最后一个值。
lag 是取当前行的下 N 条数据,并且可以设置默认值。
lead 是取当前行的上 N 条数据,并且可以设置默认值。
(3)序列:row_number、dense_rank、rank。
序列一般使用在排名上,比如说,展示销售 TOP5 的商品。
.
row_number 的功能是从第一行开始为每一行设置一个递增的数字。
rank 是跳跃排序。例如1、1、3、4,即前两行并列第1,第三行是第3,跳过了一个数字。
dense_rank 是连续排序。例如1、1、2、3,即前两行都是第1 ,第三行是2,数字连续。
select year_mon,dimShopID,amt
,row_number() over(order by amt desc ) as row_number_col
,rank() over( order by amt desc) as rank_col
,dense_rank() over(order by amt desc) as dense_rank_col
...
row_number()与rownum的区别:
row_number():over根据 列名1 分组,然后在分组内部根据 列名2 排序,row_number()计算的值就表示每组内部排序后的顺序编号,可以用于去重复值。
rownum:使用rownum进行排序的时候是先对子查询的结果集加入伪列rownum,然后再进行排序,在子查询中包含排序从句时,则先在自查询中排序,再计算自查询最终结果集的行号码。
参考文献:
(1)row_number() over(partition by 列名1 order by 列名2 desc)的使用
(2)rownum 用法
参考文献
[1] SQL之开窗函数
该篇文章是本文的主要参考文献,在本文的“相关文章”处还包含许多其他的技术中的开窗函数。例如Spark sql、Hive等。
[2] ROW_NUMBER() OVER()函数用法
该篇文章对row_number()、rank()、dense_rank()的用法进行了详细介绍。