时间戳

首页 > 解决方案 > 编写 TSQL 查询的最佳方式

sql-server - 编写 TSQL 查询的最佳方式

问题描述

我很好奇是否有更好的方法来编写这个查询，无论是在语法上更简单/更容易理解，还是为了处理速度。

我最近一直在写很多类似的查询，并希望简化这一点。我不是专业的开发人员，但我必须深入研究 sql

五分位数表

成本中心 五分位 五分位数
一个 最大限度 50
一个 第一季度 8
一个 第二季度 12
一个 第三季度 14
一个 第四季度 18
乙 最大限度 45
乙 第一季度 5
乙 第二季度 10
乙 第三季度 12
乙 第四季度 16

员工表

成本中心 员工 聘用日期
一个 W 2021-01-01
一个 X 2021-02-08
乙 是 2020-12-16
乙 Z 2021-01-15

工作完成表

员工 工作日期 小部件组装
W 2021-02-26 4
W 2021-03-05 5
X 2021-05-24 6
X 2021-05-31 3
是 2021-04-07 2
是 2021-04-14 8
Z 2021-02-07 4
Z 2021-02-14 1

我的目标：对于 workcomplete 表中的每条记录，找出员工完成工作时的任期是什么，以及它属于哪个五分之一。

员工 工作日期 小部件组装 成本中心 任期 最小五分位数 五分位
W 2021-02-26 4 一个 8 8 第一季度
W 2021-03-05 5 一个 9 12 第二季度
X 2021-05-24 6 一个 15 18 第四季度
X 2021-05-31 3 一个 16 18 第四季度
是 2021-04-07 2 乙 16 16 第四季度
是 2021-04-14 8 乙 17 45 最大限度
Z 2021-02-07 4 乙 4 5 第一季度
Z 2021-02-14 1 乙 5 5 第一季度

这就是我所做的，它工作正常：

WITH quintiles AS (
    SELECT 'A' as costcenter 
    ,'Q1' as quintile
    ,8 as quintilevalue
    UNION SELECT 'A','Q2',12
    UNION SELECT 'A','Q3',14
    UNION SELECT 'A','Q4',18
    UNION SELECT 'A','Max',50
    UNION SELECT 'B','Q1',5
    UNION SELECT 'B','Q2',10
    UNION SELECT 'B','Q3',12
    UNION SELECT 'B','Q4',16
    UNION SELECT 'B','Max',45
),

employees AS
(
    SELECT 'A' as costcenter 
    ,'W' as employee
    ,'2021-01-01' as hiredate
    UNION SELECT 'A','X','2021-02-08'
    UNION SELECT 'B','Y','2020-12-16'
    UNION SELECT 'B','Z','2021-01-15'

),

workcomplete AS
(
    SELECT 'W' as employee
    ,'2021-02-26' as workdate
    ,4 as widgetsassembled
    UNION SELECT 'W','2021-03-05',5
    UNION SELECT 'X','2021-05-24',6
    UNION SELECT 'X','2021-05-31',3
    UNION SELECT 'Y','2021-04-07',2
    UNION SELECT 'Y','2021-04-14',8
    UNION SELECT 'Z','2021-02-07',4
    UNION SELECT 'Z','2021-02-14',1


)
SELECT t.*
    ,q.quintile
FROM (
    SELECT wc.employee
        ,wc.workdate
        ,wc.widgetsassembled
        ,e.costcenter
        ,DATEDIFF(week,e.hiredate,wc.workdate) AS tenure 
        ,MIN(q.quintilevalue) as smallestquintile
    FROM workcomplete wc 
    LEFT JOIN employees e 
        ON wc.employee = e.employee
    LEFT JOIN quintiles q
        ON q.costcenter = e.costcenter and DATEDIFF(week,e.hiredate,wc.workdate) <= q.quintilevalue
    GROUP BY wc.employee
        ,wc.workdate
        ,wc.widgetsassembled
        ,e.costcenter
        ,DATEDIFF(week,e.hiredate,wc.workdate)
)t
LEFT JOIN quintiles q
    ON t.smallestquintile = q.quintilevalue and t.costcenter = q.costcenter

这也有效。

WITH quintiles AS (
    SELECT 'A' as costcenter 
    ,'Q1' as quintile
    ,8 as quintilevalue
    UNION SELECT 'A','Q2',12
    UNION SELECT 'A','Q3',14
    UNION SELECT 'A','Q4',18
    UNION SELECT 'A','Max',50
    UNION SELECT 'B','Q1',5
    UNION SELECT 'B','Q2',10
    UNION SELECT 'B','Q3',12
    UNION SELECT 'B','Q4',16
    UNION SELECT 'B','Max',45
),

employees AS
(
    SELECT 'A' as costcenter 
    ,'W' as employee
    ,'2021-01-01' as hiredate
    UNION SELECT 'A','X','2021-02-08'
    UNION SELECT 'B','Y','2020-12-16'
    UNION SELECT 'B','Z','2021-01-15'

),

workcomplete AS
(
    SELECT 'W' as employee
    ,'2021-02-26' as workdate
    ,4 as widgetsassembled
    UNION SELECT 'W','2021-03-05',5
    UNION SELECT 'X','2021-05-24',6
    UNION SELECT 'X','2021-05-31',3
    UNION SELECT 'Y','2021-04-07',2
    UNION SELECT 'Y','2021-04-14',8
    UNION SELECT 'Z','2021-02-07',4
    UNION SELECT 'Z','2021-02-14',1


)
SELECT t.*
    ,q.quintile
FROM (
    SELECT DISTINCT wc.employee
        ,wc.workdate
        ,wc.widgetsassembled
        ,e.costcenter
        ,DATEDIFF(week,e.hiredate,wc.workdate) AS tenure 
        ,MIN(q.quintilevalue) OVER (PARTITION BY wc.employee, wc.workdate) as smallestquintile
    FROM workcomplete wc 
    LEFT JOIN employees e 
        ON wc.employee = e.employee
    LEFT JOIN quintiles q
        ON q.costcenter = e.costcenter and DATEDIFF(week,e.hiredate,wc.workdate) <= q.quintilevalue

)t
LEFT JOIN quintiles q
    ON t.smallestquintile = q.quintilevalue and t.costcenter = q.costcenter

有没有更简单的方法来做到这一点，而无需嵌套选择？

标签： sql-server

解决方案

听起来您只需要一个top-1-per-group查询。

标准解决方案是使用ROW_NUMBER

WITH quintiles AS (
    SELECT 'A' as costcenter 
    ,'Q1' as quintile
    ,8 as quintilevalue
    UNION SELECT 'A','Q2',12
    UNION SELECT 'A','Q3',14
    UNION SELECT 'A','Q4',18
    UNION SELECT 'A','Max',50
    UNION SELECT 'B','Q1',5
    UNION SELECT 'B','Q2',10
    UNION SELECT 'B','Q3',12
    UNION SELECT 'B','Q4',16
    UNION SELECT 'B','Max',45
),

employees AS
(
    SELECT 'A' as costcenter 
    ,'W' as employee
    ,'2021-01-01' as hiredate
    UNION SELECT 'A','X','2021-02-08'
    UNION SELECT 'B','Y','2020-12-16'
    UNION SELECT 'B','Z','2021-01-15'

),

workcomplete AS
(
    SELECT 'W' as employee
    ,'2021-02-26' as workdate
    ,4 as widgetsassembled
    UNION SELECT 'W','2021-03-05',5
    UNION SELECT 'X','2021-05-24',6
    UNION SELECT 'X','2021-05-31',3
    UNION SELECT 'Y','2021-04-07',2
    UNION SELECT 'Y','2021-04-14',8
    UNION SELECT 'Z','2021-02-07',4
    UNION SELECT 'Z','2021-02-14',1


)
SELECT t.*
FROM (
    SELECT wc.employee
        ,wc.workdate
        ,wc.widgetsassembled
        ,e.costcenter
        ,DATEDIFF(week,e.hiredate,wc.workdate) AS tenure
        ,q.quintile
        ,ROW_NUMBER() OVER (PARTITION BY wc.employee, wc.workdate ORDER BY q.quintilevalue) as rn
    FROM workcomplete wc 
    LEFT JOIN employees e 
        ON wc.employee = e.employee
    LEFT JOIN quintiles q
        ON q.costcenter = e.costcenter and DATEDIFF(week,e.hiredate,wc.workdate) <= q.quintilevalue
)t
WHERE rn = 1;

db<>小提琴

推荐阅读