数据测试

SQL

1、时间转换


DATE(te.create_time) >= DATE_FORMAT(CURDATE(), '%Y-%m-01')

2、时间加减


te.create_time >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)  时间减法
tmt.end_time < DATE_ADD(CURDATE(), INTERVAL 1 DAY)   时间加法

3、时间间隔计算


ROUND(SUM(TIMESTAMPDIFF(SECOND, time1, time2))/3600, 1)
# 先计算秒，再转换成小时

4、字符串长度计算


IFNULL(SUM(CHAR_LENGTH(字段列)),0)

5、between操作符


# 介于数值范围内
SELECT * FROM Websites WHERE alexa BETWEEN 1 AND 20;
# 不介于数值范围内
SELECT * FROM Websites WHERE alexa NOT BETWEEN 1 AND 20;
# 文本筛选，name字段开始字母的数据
SELECT * FROM Websites WHERE name BETWEEN 'A' AND 'H';

6、并集、差集、交集


# 并集
# union，连接后去重
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
# union all，连接后不去重

# 差集
except

# 交集
intersect

7、复制表数据（新表）


# mysql、TDPG
CREATE TABLE employees_backup AS
SELECT EmployeeID, FirstName, LastName, Age, Department
FROM employees
WHERE Age > 25;
# 其他
SELECT EmployeeID, FirstName, LastName, Age, Department
INTO employees_backup
FROM employees
WHERE Age > 25;

复制表数据（已存在的表）


INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps;

窗口函数

1、语法格式


<窗口函数> OVER (
    [PARTITION BY <列名>]  -- 定义窗口的分组依据
    [ORDER BY <列名> [ASC|DESC]]  -- 定义窗口内的排序规则
    [<窗口帧子句>] -- 定义窗口的范围（如：从当前行到之后3行）
)

2、窗口函数

ROW_NUMBER() 为分区内的每一行分配一个唯一的连续序号

RANK() 为分区内的行分配排名，相同值的行获得相同排名，但会跳过后续的排名

DENSE_RANK() 为分区内的行分配排名，相同值的行获得相同排名，但不会跳过后续排名

NTILE(n) 将分区内的行平均分成 n 个桶，并为每行指定它属于哪个桶

示例：


SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_num,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank
FROM employees;

SELECT 
    *,
    SUM(salary) OVER (PARTITION BY department),
    AVG(salary) OVER (PARTITION BY department)
FROM employees;