sql-server - 将频率表转换回非频率表(取消分组)
问题描述
在 SQL Server 中,我有下表(片段),它是我收到的源数据(我无法获取它生成的原始表)。
- 年级 | 约瑟 | 存货
- 4 | 0 | 4000
- 4 | 1 | 3500
- 4 | 2 | 2000
表的第一行是 4 级,有 4,000 人服务 0 年 (YoS)。
我需要找到每个年级的平均 YoS。如果没有给我的表格汇总到 Gradelevel/YoS 级别并在 Inventory 列中有一个总和,这将很容易,但遗憾的是我没有那么幸运。
我需要取消对这个表的分组,以便我有一个新表,其中第一条记录在表中 4,000 次,下一条记录 3,500 次,接下来的 2,000 次等(库存列不会在这个新表中)。然后我可以按年级取 YoS 列的 percent_disc() 并得到中位数。然后,我还可以使用 YoS 上的其他统计功能从数据中收集其他见解。
到目前为止,我已经查看了 unpivot(似乎不是我的用例的候选对象)、CTE(找不到与我正在尝试做的事情相近的示例)和一个遍历上述内容的函数表将库存中的值指示的行数插入到一个新表中,该表成为我可以运行统计分析的“未分组”表。我相信最后一种方法对我来说是最好的选择,但我所见过的示例会迭代并专注于表中的单个列。我需要遍历每一行,然后使用 Gradelevel 和 yos 值插入 [inventory] 次数,然后再移动到下一行。
有没有人知道:
- 除了迭代/光标方法之外,还有更好的方法吗?
- 如何遍历表以实现我的目标?我一直在阅读有没有一种方法可以在不使用游标的情况下循环遍历 TSQL 中的表变量?但是我很难弄清楚如何将该迭代应用于我的用例。
编辑 10/3,这是我工作的循环代码,它产生与约翰的交叉应用相同的结果。Pro是任何统计功能都可以在上面运行,con是它很慢。
--this table will hold our row (non-frequency) based inventory data
DROP TABLE IF EXISTS #tempinv
CREATE TABLE #tempinv(
amcosversionid INT NOT null,
pp NVARCHAR(3) NOT NULL,
gl INT NOT NULL,
yos INT NOT NULL
)
-- to transform the inventory frequency table to a row based inventory we need to iterate through it
DECLARE @MyCursor CURSOR, @pp AS NVARCHAR(3), @gl AS INT, @yos AS INT, @inv AS int
BEGIN
SET @MyCursor = CURSOR FOR
SELECT payplan, gradelevel, step_yos, SUM(inventory) AS inventory
FROM
mytable
GROUP BY payplan, gradelevel, step_yos
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @pp, @GL, @yos, @inv
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @i int
SET @i = 1
--insert into our new table for each number of people in inventory
WHILE @i<=@inv
BEGIN
INSERT INTO #tempinv (pp,gl,yos) VALUES (@pp,@gl,@yos)
SET @i = @i + 1
END
FETCH NEXT FROM @MyCursor
INTO @pp, @GL, @yos, @inv
END;
解决方案
一种选择是将 CROSS APPLY 与临时计数表一起使用。这会将您的数据“扩展”成 N 行。然后,您可以执行任何所需的分析。
例子
Select *
From YourTable A
Cross Apply (
Select Top ([Inventory]) N=Row_Number() Over (Order By (Select NULL))
From master..spt_values n1, master..spt_values n2
) B
退货
Grd Yos Inven N
4 0 4000 1
4 0 4000 2
4 0 4000 3
4 0 4000 4
4 0 4000 5
...
4 0 4000 3998
4 0 4000 3999
4 0 4000 4000
4 1 3500 1
4 1 3500 2
4 1 3500 3
4 1 3500 4
...
4 1 3500 3499
4 1 3500 3500
4 2 2000 1
4 2 2000 2
4 2 2000 3
...
4 2 2000 1999
4 2 2000 2000
推荐阅读
- python - 从教程中实现玩具代码的问题
- kubernetes-helm - 使用节点选择器舵图将 pod 分配到特定节点池
- reactjs - 使用reactjs删除mongodb中的项目不起作用
- java - 无法使用 Spring Boot 设置 H2 JPA 数据库 - 错误:没有可用于自动装配的 bean
- python - 如何在 Google Cloud Run 中生成 Blob 签名 URL?
- node.js - 节点模块大小?
- java - 用spring boot rest api反应路由器
- javascript - 功能组件在浏览器刷新时中断,因为从 redux 状态中提取的对象不能按时使用
- wxwidgets - 运行使用 wxWidgets 构建的独立 exe 所需的文件
- arrays - 如何在 Swift 中将浮点数组保存为 txt 文件