sql datetime比较大小_SQL做题小技巧

数据分析必学必会当属SQL取数，毕竟要分析首先需要有数嘛。

sql的语法教程往上一搜就有很多，本文主要将介绍几个sql小技巧，灵活用上技能做对大部分的笔试题。

由于很多笔试是在牛客网上完成的，所以本文主要是用sqlite3的语法(实际上不同sql的区别主要只是在日期函数为主等函数上的差异)。

1. with as 的使用

    with as主要是为了代码简洁美观，易读易懂。主要用于创建临时表，便于此后的查询。

    比如现在有个表是用户消费表trade_tb，字段有user_id, trade_time, amount, 现在想统计找出哪一个用户的消费金额最高，并输出其消费金额。

    如果不用with as创建临时表，sql如下：

select user_id, max(total_amount) from (  select user_id, sum(amount) as total_amount  from trade_tb  group by user_id)  b-- 或者select user_id, sum(amount) as total_amountfrom trade_tbgroup by user_idorder by sum(amount) desclimit 1

上述写法可能在其他sql中不能运行，但在sqlite3中可以。一般来说，可能会通过下面的sql查询得到。

select user_id, total_amountfrom (  select user_id, sum(amount) as total_amount  from trade_tb  group by user_id)  aorder by total_amount desclimit 1;

由于中间的代码其实就是计算每个用户的总金额，可以提取成临时表，

with total_amount_tb as(    select user_id, sum(amount) as total_amount     from trade_tb     group by user_id)select user_id, total_amountfrom total_amount_tb order by total_amount desclimit 1;

with as 结构主要在代码涉及到多个临时表或者多重子查询时使用。【没有想到比较合适的例子，下次再补充】

2. 条件语句的使用

在sqlite3中，条件语句一般通过case when实现，在新版本的sqlite3中也有iif函数实现，不过笔试的平台一般版本没这么新，不支持iif函数。

假如现在有学生期末成绩表score_tb，字段为stu_id, course, score，假设每个学生每门课仅有一次考试成绩。现在想取出每个学生的语文、数学、英语考试成绩。

select stu_id,       sum(case course when '语文' then score else null end) as 语文成绩,       max(case course when '数学' then score else null end) as 数学成绩,       avg(case course when '英语' then score else null end) as 英语成绩from score_tbgroup by stu_id;

这里，由于限制了每个学生在每门课上至多有一次成绩，所以用上聚合函数sum()/max()/min()/avg()的结果都是一致的。

3. 窗口函数的使用

窗口函数一般可以为排序类的函数rank()/dense_rank()/row_number()，也可以为sum()/avg()等聚合函数。

一般的笔试题目可以通过使用排序类的函数来简化实现。

查询消费总额第3的用户：

with total_amount_tb as(    select user_id, sum(amount) as total_amount     from trade_tb     group by user_id),total_amount_rank_tb as(    select user_id,            total_amount,           rank() over (order by total_amount desc) as rnk    from total_amount_tb)select user_id, total_amountfrom total_amount_rank_tbwhere rnk = 3;

取出每门成绩前2的学生及其对应的成绩：

with score_rank_tb as(    select stu_id,            course,           score,           rank() over (partition by course order by score desc) rnk    from score_tb)select course, score, stu_id, rnkfrom score_rank_tbwhere rnk <= 2order by course

另外，注意区分rank()/dense_rank()/row_number()的区别：

select stu_id,       course,       score,       rank() over (partition by course order by score desc) rnk,       dense_rank() over (partition by course order by score desc) dense_rnk,       row_number() over (partition by course order by score desc) row_numfrom score_tb2

也就是可以看到，对于成绩相同的排序，rank()和dense_rank()取的是相同的名次，但是对于下一个成绩，rank()是累加前面的总人数得到下一个名次，而dense_rank()是累加前面的数字个数得到的下一个名次，而row_number()则表示行数，根据排序的结果给每一行标注行号。

4. 日期时间函数的使用

sql的题目有时会出现一些需要通过日期函数表达的，这时候就会出现很多问题，因为不同的sql时间函数存在一些差异，特别是sqlite3的日期函数跟其他sql差别比较大，所以为了做好笔试题，一般还得提前把日期函数复习一遍。

• 从字符串生成日期

select datetime('2020-01-01 00:12:34'),       date('2020-01-01 00:12:34'),       time('2020-01-01 00:12:34');

• 从unix时间戳生成日期

select datetime(0,'unixepoch'),       date(0,'unixepoch'),       time(0,'unixepoch');

select datetime(0,'unixepoch','utc'),       date(0,'unixepoch','utc'),       time(0,'unixepoch','utc');-- 可以看出这里假设0的我们当地的时间戳，转成日期之后，再转换成utc的时间。

select datetime(0,'unixepoch','localtime'),       date(0,'unixepoch','localtime'),       time(0,'unixepoch','localtime');-- 由于有localtime的转换，所以这里0假设的是utc的时间。

• 从日期转换成其他形式的值

strftime(format, timestring)实际上就是将日期转换成其他表示形式。

select strftime('%Y-%m-%d %H:%M:%S','2020-01-02 04:15:36') as 日期时间,       strftime('%f','2020-01-02 04:15:36') as 带小数点的秒,       strftime('%j','2020-01-02 04:15:36') as 一年第几天,       strftime('%J','2020-01-02 04:15:36') as 从JulianDay至今天数,       strftime('%s','2020-01-02 04:15:36') as 从19700101至今秒数;

• 日期加减

select datetime('now') as 当前,        datetime('now','start of month') as 月初,       datetime('now','start of month','+1 month','-1 day') as 月底,       datetime('now', '+20 days') as 二十天后,       datetime('now','+50 hours') as 五十小时后,       datetime('now', 'start of year') as 年初,       datetime('now', 'start of day') as 今天起始

掌握这些小技巧其实笔试题就能做大部分啦~