目录
顺手笔记
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,4dense_rank()
:若存在相同位次,不会跳过之后的位次。eg:1,1,1,2row_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. 多个字符串合并
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…
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’只要是一任意一个之前的周一的就行
注:如果今天是周一,那么不包括周一,因为数据库还没更新
题目:
HIVE SQL报错合集
Ambiguous column reference pay_no in temp1
TEMP1 表中有多个名为pay_no的列
解决办法:
- 重命名列名
- 删除保留一个pay_no