datediff直接算入职到当前日期会出错，因为工龄需按“年-月-日”逐级折算而非简单整年差，如2022-03-15至2024-02-10实际为1年10个月；sql server无原生age()函数，须用datefromparts与case逻辑校准生日是否已过。
为什么 DATEDIFF 直接算入职到当前日期会出错？
因为工龄不是简单的时间差，得按“年-月-日”逐级折算：比如 2022-03-15 到 2024-02-10 实际不满 2 年，只能算 1 年 10 个月。直接用 DATEDIFF(YEAR, hire_date, GETDATE()) 会忽略月份和日期的对齐，导致高估。
窗口函数本身不解决工龄逻辑，但它能帮你把计算“锚定”在每个员工的最新记录上（比如某人调岗多次，要按最后一次生效的 effective_date 算），避免用 GROUP BY 错失明细行。
- 别在
SELECT里写DATEDIFF(YEAR, ...)就完事 —— 它只是整年数，不处理进位 - 如果表里有多个历史记录（如
employee_history），用ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY effective_date DESC)先筛出最新一条，再算工龄 - SQL Server 没有原生
AGE()函数（PostgreSQL 有），得自己拼逻辑
SQL Server 中用 DATEFROMPARTS + DATEDIFF 分步算年月日
核心思路是：先算整年，再用该年份的同月同日去比对，看是否已过生日；没过就减 1 年，再算剩余月份。这个逻辑不能靠单个窗口函数完成，但可以套在 OVER() 的上下文中复用。
SELECT
emp_id,
hire_date,
GETDATE() AS as_of_date,
DATEDIFF(YEAR, hire_date, GETDATE()) -
CASE
WHEN DATEFROMPARTS(YEAR(GETDATE()), MONTH(hire_date), DAY(hire_date)) > GETDATE()
THEN 1 ELSE 0
END AS years,
DATEDIFF(MONTH,
DATEFROMPARTS(
YEAR(GETDATE()) - CASE WHEN DATEFROMPARTS(YEAR(GETDATE()), MONTH(hire_date), DAY(hire_date)) > GETDATE() THEN 1 ELSE 0 END,
MONTH(hire_date),
DAY(hire_date)
),
GETDATE()
) % 12 AS months
FROM employees;
注意：DATEFROMPARTS 在 SQL Server 2012+ 可用；如果 hire_date 含时分秒，先用 CAST(hire_date AS DATE) 清洗。
- 别漏掉
CAST(... AS DATE)，否则DATEFROMPARTS和带时间的hire_date比较会出偏差 -
% 12是为了防止跨年时月份算成负数或超 12 - 这个表达式不能直接放进
OVER(PARTITION BY ...)里当窗口函数用 —— 它本身不含聚合，只是逐行计算
当需要按部门统计平均工龄时，为什么不能直接 AVG(years)？
因为工龄是偏态分布：新人多、老人少，直接算算术平均会掩盖结构。更合理的是先用窗口函数标记每人的工龄区间，再聚合。
SELECT
dept_id,
COUNT(*) FILTER (WHERE years 5) AS senior
FROM (
SELECT
dept_id,
emp_id,
DATEDIFF(YEAR, hire_date, GETDATE()) -
CASE WHEN DATEFROMPARTS(YEAR(GETDATE()), MONTH(hire_date), DAY(hire_date)) > GETDATE() THEN 1 ELSE 0 END AS years
FROM employees
) t
GROUP BY dept_id;
上面用了 PostgreSQL 风格的 FILTER；SQL Server 要改用 SUM(IIF(...))。关键是：窗口函数在这里没出场，但它的思想（先算每人、再按组归总）决定了你不能跳过逐行计算这步。
- 别试图用
AVG( DATEDIFF(...) ) OVER (PARTITION BY dept_id)—— 这算出来是“部门内每人年份的平均值”，不是“部门平均工龄”，语义错位 - 如果要加权平均（比如按岗位职级加权），得先用
LAG()或LEAD()补全历史职级变更时间点，再切片计算各段工龄
Oracle 和 PostgreSQL 用户可以直接用 MONTHS_BETWEEN 或 AGE()
Oracle 的 MONTHS_BETWEEN(date1, date2) 返回精确到小数的月份数（如 25.8），除以 12 就是年份，再取整或格式化即可；PostgreSQL 的 AGE(NOW(), hire_date) 直接返回 1 year 10 mons 25 days 这样的 interval，EXTRACT(YEAR FROM AGE(...)) 拿年份。
但注意：这些函数在窗口中可用，但结果仍是逐行的 —— OVER() 不改变函数本身的语义，只控制分区范围。如果你写 EXTRACT(YEAR FROM AGE(NOW(), hire_date)) OVER (PARTITION BY dept_id)，得到的还是每人自己的年份，不是部门平均。
- Oracle 中
MONTHS_BETWEEN对日期顺序敏感：后减前为正，前减后为负，别写反参数 - PostgreSQL 的
AGE()会自动处理闰年、大小月，比手写逻辑稳；但导出到 BI 工具时，interval 类型可能被转成字符串，需显式TO_CHAR(..., 'FMYYYY "years" FMMM "months"') - MySQL 用户没这些函数，老实用
TIMESTAMPDIFF(YEAR, hire_date, CURDATE())配合LAST_DAY()手动校验月份边界
实际业务中最容易被忽略的，是试用期是否计入工龄、产假/停薪留职期间是否暂停计算、以及系统中 hire_date 字段到底是合同签订日、到岗日还是社保首次缴纳日 —— 这些都不是 SQL 能自动识别的，得靠上游数据清洗和字段注释来保证。
