sql datetime字段 取年月日_BAT 商业分析基本功之 SQL基础

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作，SQL语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。由于它具有功能丰富、使用方便灵活、语言简洁易学等突出的优点，是大部分互联网公司BI部门常用的数据库语言。

SQL作为一门基础的数据提取语言，其使用场景和方法主要表现在以下几个方面：数据查询、聚合与连接、函数应用

数据查询

SQL 作为数据查询语言（DQL:Data Query Language），其语句也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。不同用户群体对 SQL 的侧重点是有差异的，但无论是哪一个群体，基本都绕不开数据查询语句，是最为基础的内容。保留字Select是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。SQL 作为面向数据库表的基础语言，不同用户群体对 SQL 的侧重点是有差异的，但无论是哪一个群体，基本都绕不开数据查询语句，是最为基础的内容。

基础查询： 基础的查询语句Select，其实就是简单的跟数据库对话的过程。不管是哪种 SQL 的拓展语言，基础查询里的语法基本都一致。如*代表全量查询， distinct 代表去重，top 和 limit 代表数据条数限定， as 代表原表字段名进行替换更新。

SELECT <*,DISTINCT column [alias],...> FROM table；

条件查询：在基础查询上添加一些约束条件，就是条件查询。如用关键字 where 查看符合某种条件下的数据，用 like 或者 relike 来做正则匹配，用 between 或者 in 来限制一个范围等

SELECT <*,DISTINCT column [alias],...> FROM table

       [WHERE condition(1)]

       [And condition(s)]；

排序查询： 在执行查询结果时，默认情况下查询结果无序排列，但我们有时需要对数据按一定规则进行排序。这时可以通过ORDER BY子句来实现这个功能。

SELECT <*,column [alias],...> FROM table

       [WHERE condition(s)]

       [ORDER BY column[ASC|DESC]]；

#默认是ASC指定的升序排列，DESC用来指定降序排列

聚合与连接

前面讲数据查询语句，不管怎么查询，其实并不影响原生的表结构，即原来的表是按照什么逻辑写的数据，查询结果里的数据也是基于这种逻辑，只是筛选了局部数据而已。但数据聚合与连接就不一样了，聚合会在纵向上改变原生表结构，连接则在横向上拓展了表结构。

数据聚合：随着表中记录（数据行）的不断积累，存储数据逐渐增加，我们希望计算出这些数据的合计值或者平均值等。要对一张表做数据聚合，其实理解了两个概念即可，维度和指标。维度是你要基于哪些字段来做聚合，指标是在这个维度之上，你想用什么汇总函数生成哪些指标。数据聚合的关键字是 group by，维度里的属性值仍来自于原生表，指标则是新生成的汇总值。

SQL主要聚集函数

SELECT Count(*) as 数量
       MIN(column a) as 最小值
       MAX(column a) as 最大值
       AVG(column *) as 平均值
FROM table
       [WHERE condition(s)]
       [Group BY column[*]]；

数据连接： 对多张表做连接，是 SQL 里面非常重要的一个内容，数据连接可分为以下几类：内连接、外连接、交叉连接。连接方式可以参考下图。

函数应用

函数库，其实就像是一个数据处理与分析的百宝箱，收藏着各种场景下需要用到的车轮子。对函数库的熟悉和掌握，可以较好地提升工作效率，也让计算脚本显得轻量而简洁。毕竟站在通用的函数的肩膀上，很多统计逻辑是可以一步到位的，不需要沉迷于山重水复的自主构造里。以下为经常使用到的函数。

时间和日期函数： 时间日期函数主要使用场景有：时间取值和加减。其中时间取值函数则是在一个详细的时间戳里，取出自己想要的部分，如 year，month，day，hour 等时间；时间加减则主要是按日维度 date_diff，date_add，date_sub 等，按月维度有 month_between，add_months 等。

to_date(string timestamp) 返回时间中的年月日： to_date(“1970-01-01 00:00:00″) = “1970-01-01″
to_dates(string date) 给定一个日期date，返回一个天数（0年以来的天数）
year(string date) 返回指定时间的年份，范围在1000到9999，或为”零”日期的0。
month(string date) 返回指定时间的月份，范围为1至12月，或0一个月的一部分，如’0000-00-00′或’2008-00-00′的日期。
day(string date) dayofmonth(date) 返回指定时间的日期
hour(string date) 返回指定时间的小时，范围为0到23。
minute(string date) 返回指定时间的分钟，范围为0到59。
second(string date) 返回指定时间的秒，范围为0到59。
weekofyear(string date) 返回指定日期所在一年中的星期号，范围为0到53。
datediff(string enddate, string startdate) 两个时间参数的日期之差。
date_add(string startdate, int days) 给定时间，在此基础上加上指定的时间段。
date_sub(string startdate, int days) 给定时间，在此基础上减去指定的时间段。

聚合函数： 在数据聚合中，选择了具体字段作为聚合维度后，之后便是应用各种聚合函数得到汇总值的过程。其中有简单聚合函数如 count 计数，sum 求和，avg 求平均，也可以基于分布特征，max/min 取极值，std 取标准差，variance 取方差，另外若在聚合过程中涉及分区处理的话，也有 rank，first/last_value，row_number 等函数可以应用。

row_number() 是没有重复值的排序(即使两天记录相等也是不重复的)，可以利用它来实现分页
dense_rank() 是连续排序，两个第二名仍然跟着第三名
rank() 是跳跃排序，两个第二名下来就是第四名

数学函数： SQL 里的数学函数主要和数值处理有关，有取值函数和变换函数等。取值函数包括 round 四舍五入，abs 取绝对值，ceil 向上取整等，主要用于对具体数值的细节调整；变换函数则会改变该字段的数据分布形态，如正弦 sin，余弦 cos，或者开根号 sqrt 等。

round(double a) 四舍五入
round(double a, int d) 小数部分d位之后数字四舍五入，例如round(21.263,2),返回21.26
floor(double a) 对给定数据进行向下舍入最接近的整数。例如floor(21.2),返回21。
ceil(double a), ceiling(double a) 将参数向上舍入为最接近的整数。例如ceil(21.2),返回23.

文本处理： 数据类型可以粗糙地分为数值数据和文本数据，对于文本数据的处理，也有很多对应的函数。其中有一些简单取值函数，如通过 length 和 size 获得字段长度和数组大小，通过 upper 和 lower 可以切换大小写；字符串的切割与拼接，由浅入深有 split，substr，concat，wm_concat 等；最后正则表达式也是文本处理中一个特别重要的模块。