SPARK_SQL计算两个日期之间差多少年 sql计算两个日期相差天数

1.日期差

DATEDIFF(a.recordDate,b.recordDate)  a-b
或
TIMESTAMPDIFF
TIMESTAMPDIFF能干什么，可以计算相差天数、小时、分钟和秒，相比于datediff函数要灵活很多。
格式是时间小的前，时间大的放在后面。 计算相差天数：
select w1.Id
from Weather as w1, Weather as w2
where TIMESTAMPDIFF(DAY, w2.RecordDate, w1.RecordDate) = 1 
AND w1.Temperature > w2.Temperature

统计不重复数据个数
count(distinct user_id)

2.条件语句

IF(条件表达式,值1,值2)

--简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END

--Case搜索函数
CASE WHEN sex = '1' THEN '男'
     WHEN sex = '2' THEN '女'
ELSE '其他' END

例
UPDATE Salary SET sex = 
CASE sex 
WHEN 'm' THEN 'f' 
WHEN 'f' THEN 'm' 
ELSE sex END

3.IFNULL.

IFNULL(b.num, 0)
如果b.num为null，返回0，否则返回b.null自身的值

4.group_concat()

SELECT 
dept_id,
GROUP_CONCAT(name ORDER BY age DESC SEPARATOR '*') -- 分组中的name中的多行数据将按照age降序进行连接，分隔符为 * 
FROM employee2
GROUP BY dept_id;
得到
1 | 小肖*小玉*张山*小张*李四*小肖         
2 | 小东*小肖*小胡*王武*小林*猪小屁     
3 | 小非                                 
4 | 晓飞 

例
select sell_date, 
count(distinct product) as num_sold,
GROUP_CONCAT( distinct product ORDER BY product separator ',') as products
注意若需要合并的数据有重复 要加 distinct

5.like

写一条 SQL 语句，查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1

select * 
from Patients 
where conditions like 'DIAB1%' or conditions LIKE '% DIAB1%'



like模糊查询，支持%和下划线匹配，%匹配多个字符，_下划线：任意一个字符
示例：

1、查询名字中含有张的学生信息
select * from student where sname like ‘%张%’；
2、查询名字以张开头的学生信息
select * from student where sname like ‘张%’；
3、查询名字以人结尾的学生信息
select * from student where sname like ‘%人’；
4、查询名字中第二个字为心的学生信息
select * from student where sname like ‘_心%’；
5、查询名字中第三个字为心的学生信息
select * from student where sname like ‘__心%’；

因为下划线在sql中有特殊含义，所以当查询姓名中有下划线的学生信息时需要转义
示例：
select * from student where sname like ‘%_%’;

6. 顺序问题

一般，WHERE在前，GROUP BY在后，即先进行筛选，然后进行分组；
HAVING只能跟在GROUP BY之后，对分组后的聚合结果进行筛选；
HAVING的前提是分组；WHERE在最前，最先对原始数据进行一遍筛选；
WHERE的条件里只能用已有的列进行条件判断，不允许使用聚合函数。
HAVING之后可以允许使用聚合函数；
聚合函数包括count(),sum(),avg(),max(),min()


SELECT A.name,COUNT(order_number) AS count_sum
FROM orders A, customer B
WHERE A.coustomer_id=B.id
GROUP BY customer_id
HAVING COUNT(order_number)>5
ORDER BY count_sum DESC
LIMIT 5;

7. 字符串相关操作

length(a)-------获取字符串a的长度
concat(a,b..)---组合a,b..等参数
upper(a)-------将字符串a转成大写
lower(a)-------将字符串a转成小写

----------------取左边count个字符，比如left(a,2), 则取左边两个字符
left(a, count)--取左边count个字符，比如left(a,2), 则取左边两个字符
right(a,n) -----取右边长度为n的字符串，
比如right(a, length(a) - 2)，则取从左边第三个字符串开始的字符串

substring(a, start)-------------截取从start开始的右边的字符串
substring(a, start, length)-----截取从start开始长度为length的字符串

注意
substring(a, 1, 1) 等效于 left(a, 1)

8. UNION运算符

UNION运算符用于组合两个或更多SELECT语句的结果集，而不返回任何重复的行。

1.UNION中的每个SELECT语句必须具有相同的列数
2.这些列也必须具有相似的数据类型
3.每个SELECT语句中的列也必须以相同的顺序排列
4.每个SELECT语句必须有相同数目的列表达式
5.但是每个SELECT语句的长度不必相同
注释：默认情况下，UNION运算符选择一个不同的值。如果允许重复值，请使用UNION ALL。

-----例：
select name,population,area from World
where population > 25000000 or area > 3000000
-----等效于
select name,population,area from World
where population > 25000000
union
select name,population,area from World
where area > 3000000



----------例如：----------
输入：
Products table:
+------------+--------+--------+--------+
| product_id | store1 | store2 | store3 |
+------------+--------+--------+--------+
| 0          | 95     | 100    | 105    |
| 1          | 70     | null   | 80     |
+------------+--------+--------+--------+
输出：
+------------+--------+-------+
| product_id | store  | price |
+------------+--------+-------+
| 0          | store1 | 95    |
| 0          | store2 | 100   |
| 0          | store3 | 105   |
| 1          | store1 | 70    |
| 1          | store3 | 80    |
+------------+--------+-------+
解释：
产品0在store1，store2,store3的价格分别为95,100,105。
产品1在store1，store3的价格分别为70,80。在store2无法买到。


select product_id, 'store1' as store, store1 as price 
from Products 
where store1 is not null
union 
select product_id, 'store2' as store, store2 as price 
from Products 
where store2 is not null
union 
select product_id, 'store3' as store, store3 as price 
from Products 
where store3 is not null

9. LIMIT

1、当 limit后面跟一个参数的时候，该参数表示要取的数据的数量

例如 select* from user limit 3 表示直接取前三条数据

2、当limit后面跟两个参数的时候，第一个数表示要跳过的数量，后一位表示要取的数量,例如

select * from user limit 1,3;

就是跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据

3、当 limit和offset组合使用的时候，limit后面只能有一个参数，表示要取的的数量,offset表示要跳过的数量 。

例如select * from user limit 3 offset 1;表示跳过1条数据,从第2条数据开始取，取3条数据，也就是取2,3,4三条数据

10. 排名

1. rank() over
   作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
   说明：例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。即：1 1 3 4 5 5 7
2. dense_rank() over
   作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
   说明：和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次。即：1 1 2 3 4 5 5 6
3. row_number() over
   作用：查出指定条件后的进行排名，条件相同排名也不相同，排名间断不连续。
   说明：这个函数不需要考虑是否并列，即使根据条件查询出来的数值相同也会进行连续排序。即：1 2 3 4 5 6

使用小提示
dense_rank() over 后面跟排序的依据的列，下面是用了一个排序好的列(order by score desc)。
注意：如果select中有一列是用rank()这类函数，其他的列都会按着他这列规定好的顺序排。

select score, 
dense_rank() over (order by score desc) as 'rank'  
#这个rank之所以要加引号，因为rank本身是个函数，直接写rank会报错
from scores;

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

11. 前后函数：LAG(expr,n)、LEAD(expr,n)

用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值（以当前行为原点）

--寻找连续出现三行的值
select distinct num as ConsecutiveNums 
from (select num, 
             lag(num, 1, null) over (order by id) lag_num, //取前一行的num
             lead(num, 1, null) over (order by id) lead_num//取后一行的num
      from logs) l
where l.Num = l.lag_num     // 条件：三行值相同
  and l.Num = l.lead_num