SQL学习笔记

顺手笔记

1. 时间戳转为日期

from_unixtime(CAST((`time`/1000 + 8*3600) AS bigint),'yyyy-MM-dd HH:mm:ss')

2. 日期转为时间戳

unix_timestamp()

注：这个函数有个坑，得到的时间戳会晚8个小时
改进，减去8小时的时间戳（28800000毫秒）：

unix_timestamp(CURRENT_DATE())-28800000

计算月份差

select
months_between('2022-01-01', '2022-02-08')
from t1

SQLite 语法

https://blog.csdn.net/webouse/article/details/52585951

• SQLite的SQL语句中没有 iff 函数，要使用 case when then end代替。

一、表合并

• join: 内连接，如果表中有至少一个匹配，则返回行

• left join: 左连接，即使右表中没有匹配，也从左表返回所有的行，如果右表有多个匹配，左表会膨胀

• right join: 右连接，即使左表中没有匹配，也从右表返回所有的行，如果左表有多个匹配，右表会膨胀

• full join: 全连接，只要其中一个表中存在匹配，就返回行

• union：上下合并（注：字段一定要一一对应得上，字段顺序也要一样）（会取并集）

• union 和 union all
  union和union all用于将表上下拼接，注意事项：
  字段名称必须一致
  字段顺序必须一致
  没有连接条件
  记得对union之后的表重命名
  union和union all的区别在于，union会对结果去重并且按字段排序，union all只是简单拼接，不去重不排序

SELECT * FROM TEMP_RESULT1
UNION
SELECT * FROM TEMP_RESULT2

二、排序：

• rank()：若存在相同位次，则会跳过之后的位次。eg：1,1,1,4
• dense_rank()：若存在相同位次，不会跳过之后的位次。eg：1,1,1,2
• row_number()：若存在相同的位次也会给予不同的数字，例如，有3条排在第一位时：1,2,3,4

窗口函数用法：
<窗口函数> over ([paritition by <列清单> ]
order by <排序用列清单>)
*其中[]中的内容可以忽略
示例： dense_rank() over(order by salary desc)

select emp_no,salary,
       dense_rank() over(order by salary desc) as 'rank'
from salaries
where to_date='9999-01-01'
group by emp_no

窗口函数分组后，找出排序后不一样的列，并计算相差的时间间隔。
解决办法：通过偏移lead/lag 找出不一样的行，并计算相差的时间间隔。

三、字符串操作

1. 字符串拼接：||

select "a" || "b"
>>> "ab"

2. 字符串索引选取

选取字符串某几个：

select substr("aaabcc", -2, 2) # 从倒数第二位开始选两个
>>> "cc"

对于mysql还可以直接这么做：

select right("aaabcc", 2)   # 从右边开始选两个
>>> "cc"
select left("aaabcc", 2)    # 从左边开始选两个
>>> "aa"

3. 多个字符串合并

牛客题霸–SQL53 按照dept_no进行汇总

select dept_no, group_concat(col1, ',') from table1 group by col2

HIVE SQL:

concat_ws('_',collect_set(col1))

如果报错了，说什么 must be "string or array<string>", but "array<int>" was found.，那么要把字段转为字符串格式再合并，即concat_ws('_',collect_set(STRING(col1)))

4. 字典 json

Hive sql 解析Json数组
hive sql

get_json_object(T1.col, '$.key')

SELECT get_json_object('[{"website":"www.iteblog.com","name":"过往记忆"}, {"website":"carbondata.iteblog.com","name":"carbondata 中文文档"}]', '$.[0].website');

字符串补足 填充空白

lpad(col, 3, ' ') -- 左补足
rpad(col, 3, ' ') -- 右补足

四、分页查询

limit n, m：从第n条数据开始提取m条数据。

五、布尔索引 exists

牛客网–SQL57 使用含有关键字exists查找未分配具体部门的员工的所有信息

select * from employees as e
where not exists(
    select emp_no from dept_emp as d
    where d.emp_no = e.emp_no
    )

exists返回的是布尔列，即对于每个e.emp_no，是否满足有d.emp_no=e.emp_no，如果满足，则返回True，否则返回False。相反，有not exists函数。

六、if…elseif…

牛客题霸–SQL59 获取有奖金的员工相关信息。

select e.emp_no, e.first_name, e.last_name, b.btype, s.salary,
    (case b.btype
     when 1 then s.salary * 0.1
     when 2 then s.salary * 0.2
     else s.salary * 0.3 end
        ) as bonus
from employees as e inner join emp_bonus as b using(emp_no) /*因为要获取有奖金的员工，所以要用inner join，确保只有出现在emp_bonus的员工才能提取出来*/
    left join salaries as s using(emp_no)
where s.to_date = '9999-01-01'

多列转行，数组元素转行—unnest

unnest：https://blog.csdn.net/hongchangfirst/article/details/90724869

七、日期函数

date.add()

使用函数date_add(column, interval n day)增加了n天。

select *, date_add(login_date, interval 2 day) as two_days_later from test_user_login_3days;

新字段two_days_later就是增加了2天。

+---------+------------+----------------+
| user_id | login_date | two_days_later |
+---------+------------+----------------+
|     123 | 2018-08-02 | 2018-08-04     |
|     123 | 2018-08-03 | 2018-08-05     |
|     123 | 2018-08-04 | 2018-08-06     |
+---------+------------+----------------+

lad()、lead()

https://www.freesion.com/article/38331376363/

八、行索引

用row_number() over()来生成行索引。

九、定义变量并使用

https://www.gairuo.com/p/hive-sql-variables

-- set @a = '2021-01-01';
-- select * from employees
-- where hire_date = @a;

select * from web_logs
where country_code = "${country_code}"

十、临时表

with table_name as (select ... from ...)
SQL79 牛客的课程订单分析(三)

with moreThan1 as
(
    select user_id
    from order_info
    where datediff(date,"2025-10-15")>0
      and product_name in ("C++","Java","Python")
      and status ="completed"
    group by user_id
    having count(id)>1
)
 
select *
from order_info
where datediff(date,"2025-10-15")>0
      and product_name in ("C++","Java","Python")
      and status="completed"
      and user_id in (select * from moreThan1)
order by id;

创建多张临时表：
with table1 as (), table2 as ()...
SQL81 牛客的课程订单分析(五)

在这里插入代码片with t1 as (
    select *, row_number() over(partition by user_id order by date) as rank,
        lead(date, 1, null) over(partition by user_id order by date) as second_buy_date
    from order_info
    where date > '2025-10-15'
        and status = 'completed'
        and product_name in ('C++', 'Java', 'Python')
    ),

t2 as (
    select user_id, count(user_id) as cnt
    from t1
    group by user_id
    )

select t1.user_id, date as first_buy_date, second_buy_date, cnt
from t1
left join t2
on t1.user_id = t2.user_id
where t1.rank = 1
    and cnt >= 2

sql临时表、创建虚拟表、select临时表、多行数据、自定义数据

SELECT * FROM (
  VALUES ('John', 25),('Jane', 30),('Mike', 35)
) AS table_name(name, age);
# 方法2:
select 1,2
union all
select 3,4

十一、group by 后 case when 生成多个字段——pivot

https://blog.csdn.net/Andy_shenzl/article/details/106279865

SELECT  province
        ,SUM(IF(time='q1',gdp,NULL)) q1
        ,SUM(IF(time='q2',gdp,NULL)) q2
        ,SUM(IF(time='q3',gdp,NULL)) q3
        ,SUM(IF(time='q4',gdp,NULL)) q4
FROM    pivot_test
GROUP BY province

十二、LEFT JOIN AND 和 LEFT JOIN 后 WHERE 的区别

left join ... and 是提前对T2进行筛选，然后拿来和T1连接。
left join ... where是对连接后的表进行筛选。
结果区别是：前者会展示T1的全主键，后者可能会把T1的部分主键筛掉。

案例：假设id=2的deleted_id=1

select * from t1
left join t2 on t1.id = t2.id and t2.deleted_id = 0

id	deleted_id
1	0
2	NULL
3	0
4	0

select * from t1
left join t2 on t1.id = t2.id
where t2.deleted_id = 0

id	deleted_id
1	0
3	0
4	0

获取最近一个星期一的日期

SELECT 
-- yyyyMMdd格式
DATE_FORMAT(DATE_ADD(DATE_ADD(CURRENT_DATE(),-1), -(DATEDIFF(DATE_ADD(CURRENT_DATE(),-1), '2022-01-03')%7)), 'yyyyMMdd')
-- yyyy-MM-dd格式
DATE_ADD(DATE_ADD(CURRENT_DATE(),-1), -(DATEDIFF(DATE_ADD(CURRENT_DATE(),-1), '2022-01-03')%7))

其中 '2022-01-03’只要是一任意一个之前的周一的就行
注：如果今天是周一，那么不包括周一，因为数据库还没更新

题目：

如何计算某段时间内的n日留存率？

HIVE SQL报错合集

Ambiguous column reference pay_no in temp1

TEMP1 表中有多个名为pay_no的列
解决办法：

1. 重命名列名
2. 删除保留一个pay_no