sql - 跨垃圾维度计数和分组

Question

每年我们都会从政府部门收集员工数据，这些数据是从 staging 转换并存储在一个表 Employee_Fact 中的。此表包含一个唯一的员工 ID 以及员工的七个休假相关项目的当前休假余额。为了让我的问题更简单，我在这里只包括三个；病假、娱乐假和照顾者假。

大多数员工在这些休假属性中都有一个数值（有效值），但是有些员工无权享受某些类型的休假（不适用），或者雇主没有将他们的休假权利放入系统中（缺失），或者他们不想透露他们的假期余额（撤回）。不幸的是，有人决定将这些类型的值编码为负数。因此，例如，缺少病假的员工最初将在临时表中的病假列中具有 -7777（或 -8888 表示不适用，或 -9999 表示已撤回）。此编纂适用于七个与休假相关的列。

当数据被转换并推送到 Employee_Fact 表时，这些负值将被取消，因此它们不会意外地包含在合法数字休假值的总和中，事实表看起来像这样。

员工_PK	Rec_Leave_Balance	Sick_Leave_Balance	Carers_Leave_Balance	Employee_Leave_SK	时期
101	10	20	15	1	2020
102	无效的	14	无效的	2	2020
103	无效的	无效的	无效的	3	2020
104	15	17	19	1	2020
105	无效的	无效的	无效的	4	2020
101	10	无效的	20	5	2021
102	15	无效的	18	5	2021
103	无效的	无效的	20	6	2021
104	12	44	无效的	7	2021
105	无效的	无效的	5	6	2021

但是，仍然需要找出每年每个休假项目有多少员工“失踪”、“不适用”等。为了实现这一点，创建了一个 Employee_Leave 维度，其中包含员工可能拥有的七个休假项目的所有可能组合。此维度中的 PK 是 Employee_Fact 表中的代理键。维度中有大约 78,000 种可能的组合，因为每个休假项目都可以归类为有效值、缺失、不适用或已撤销。Employee_Leave 维度表的简化版本如下所示：

Employee_Leave_PK	Sick_Leave_Code	Sick_Leave_Desc	Recreation_Leave_Code	Recreation_Leave_Desc	Carers_Leave_Code	Carers_Leave_Desc
1	0	有效值	0	有效值	0	有效值
2	-7777	失踪	0	有效值	-7777	失踪
3	-7777	失踪	-7777	失踪	-7777	失踪
4	-8888	不适用	-8888	不适用	-8888	不适用
5	0	有效值	-7777	失踪	0	有效值
6	-9999	撤销	-9999	撤销	0	有效值
7	0	有效值	0	有效值	-8888	不适用

我被要求在 QlikView 文档的每个休假类别中显示缺失、不适用和撤回值的计数，我想用 SQL 查询交叉检查这些数字。

这段代码使用联合查询为我提供了下面的结果集，但我想知道是否有更好的方法来做到这一点，它将行作为列，将计数和年份作为值。我不认为支点有效，因为我有多个领域需要支点。

`
SELECT [Period],
Case 
WHEN Sick_Leave_Desc like 'Missing' then 'Sick Leave - Missing'
WHEN Sick_leave_Desc like 'Not Applicable' then 'Sick Leave - Not applicable'
WHEN Sick_leave_Desc like 'Withdrawn' then 'Sick Leave - Withdrawn'
WHEN Sick_Leave_Desc like 'Valid Value' then 'Sick Leave - Valid Value'
END
AS Leave_type_value,
COUNT(Employee_PK) AS Total_Employees FROM
DM.FACT_EMPLOYEE_TEST FE
INNER JOIN
DM.DIM_EMPLOYEE_LEAVE DE
ON FE.Employee_Leave_SK=DE.Employee_Leave_PK
GROUP BY 
[Period],Sick_Leave_Desc

UNION ALL

SELECT [Period],
Case 
WHEN DE.Recreation_Leave_Desc like 'Missing' then 'Recreation Leave - Missing'
WHEN DE.Recreation_Leave_Desc like 'Not Applicable' then 'Recreation Leave - Not applicable'
WHEN DE.Recreation_Leave_Desc like 'Withdrawn' then 'Recreation Leave - Withdrawn'
WHEN DE.Recreation_Leave_Desc like 'Valid Value' then 'Recreation Leave - Valid Value'
END
AS Leave_type_value,
COUNT(Employee_PK) AS Total_Employees FROM
DM.FACT_EMPLOYEE_TEST FE
INNER JOIN
DM.DIM_EMPLOYEE_LEAVE DE
ON FE.Employee_Leave_SK=DE.Employee_Leave_PK
GROUP BY 
[Period], Recreation_Leave_Desc

UNION ALL
SELECT [Period],
Case 
WHEN DE.Carers_Leave_Desc like 'Missing' then 'Carers Leave - Missing'
WHEN DE.Carers_Leave_Desc  like 'Not Applicable' then 'Carers Leave - Not applicable'
WHEN DE.Carers_Leave_Desc  like 'Withdrawn' then 'Carers - Withdrawn'
WHEN DE.Carers_Leave_Desc  like 'Valid Value' then 'Carers - Valid Value'
END
AS Leave_type_value,
COUNT(Employee_PK) AS Total_Employees FROM
DM.FACT_EMPLOYEE_TEST FE
INNER JOIN
DM.DIM_EMPLOYEE_LEAVE DE
ON FE.Employee_Leave_SK=DE.Employee_Leave_PK
GROUP BY 
[Period],Carers_Leave_Desc

`

时期	离开类型值	Total_Employees
2020	病假 - 失踪	2
2020	病假 - 不适用	1
2020	病假 - 有效值	2
2021	病假 - 有效值	3
2021	病假 - 撤回	2
2020	休闲假 - 失踪	1
2021	休闲假 - 失踪	2
2020	休闲假 - 不适用	1
2020	休闲假 - 有效值	3
2021	休闲假 - 有效值	1
2021	娱乐假 - 撤回	2
2020	照顾者离开 - 失踪	2
2020	照顾者休假 - 不适用	1
2021	照顾者休假 - 不适用	1
2020	照顾者 - 有效值	2
2021	照顾者 - 有效值	4

标签： sqlsql-servertsqldata-warehouseolap