SQL常用语句基础：（1）时间函数与表格操作

---

前言

SQL语言是常用的数据库查询语言。本文主要记录开发过程中常见的一些语句和技巧，便于自查和分享。（本文的语言环境为MaxCompute SQL)

---

一、时间相关

1. 时间戳处理

• 13位需先转成10位

select
to_char(from_unixtime(cast(substr('1661348204758',1,10) as int)), 'yyyymmdd')
from_unixtime(cast(1661348204758/1000 as int))

2. 时间转码

• 时间转码

from_unixtime(a.dateline) 
-- 'yyyymmdd hh:mi:ss'

3. 时间函数

• 时间加减（如何查询近xx天的范围）

-- 查询近30天的数据
dt BETWEEN to_char(dateadd(to_date('${bizdate}', "yyyymmdd"), -29, 'dd'),"yyyymmdd") AND '${bizdate}'

• 时间拼接（将日期与起始时分秒字段拼接后，对应时间戳发生时间进行查询）

to_char(from_unixtime(a.dateline), 'yyyymmdd hh:mi:ss') BETWEEN concat(dim_a.dt, ' ', dim_a.start_time)
           AND
           concat(dim_a.dt, ' ', dim_a.end_time)

二、表格相关

1. 建表

• 建表语句（以日期为分区）

主要有三种类型的字段

字段类型	对应英文符号
文本	STRING
整数	BIGINT
小数	DOUBLE

CREATE TABLE IF NOT EXISTS db_ads.table_df(
    did STRING COMMENT '用户did'
    ,group_id BIGINT COMMENT '分组id'
    ,pay_amt DOUBLE COMMENT '消费金额'
) comment '人群包分天全量表' PARTITIONED BY (dt STRING COMMENT '分区字段，yyyyMMdd，分区字段') 

2. 表格更新

• 添加列

ALTER TABLE db_ads.table_df ADD COLUMNS (
    `active_code_file_id` STRING COMMENT '激活码文件id，20221115起生效')

• 删除列

ALTER TABLE db_ads.table_df DROP COLUMNS active_code_file_id

• 清除表格的内容（保留表结构）

TRUNCATE TABLE db_ads.table_df

• 清除表格（删除表结构）

drop table if exists db_ads.table_df

3. 检查表格

• 检查表格结构

desc db_ads.table_df

• 检查是否上传成功

select
*
from
db_ads.table_df

三、开窗函数

• rank函数

rank() over(partition by a.if_gift_prop_pay,a.uid order by a.hh asc) as uid_rn

• row_number 函数

row_number() over(partition by a.if_gift_prop_pay,a.uid order by a.hh asc) as uid_rn