sql - SQL 运行总组与总阈值重置
问题描述
我有桌子:
PersonID FirstName PersonAge
1 Pras 2
2 Deep 3
3 Test 4
4 Prash 2
5 ser 1
6 df 8
7 ddf 5
8 vvv 4
9 ddd 1
10 eww 6
11 vvv 3
12 vbbb 7
13 Prabbbbs 6
我想对它们进行分组,使组的总年龄不超过 10 岁。
我可以通过递归来做到这一点,但在较大的表上效率低下。
;WITH cte AS
(
SELECT PersonID, PersonAge, 1 AS [Group], PersonAge AS RunningTotal FROM POP where PersonId=1
UNION ALL
SELECT data.PersonId, data.PersonAge,
CASE WHEN cte.RunningTotal + data.PersonAge > 10 THEN cte.[Group] + 1 ELSE cte.[Group] END,
-- Reset the running total for each new group
data.PersonAge + CASE WHEN cte.RunningTotal + data.PersonAge > 10 THEN 0 ELSE cte.RunningTotal END
FROM POP data INNER JOIN cte ON data.PersonId = cte.PersonID + 1
)
SELECT * FROM cte
所以,我需要的输出将是:
PersonID PersonAge Group RunningTotal
1 2 1 2
2 3 1 5
3 4 1 9
4 2 2 2
5 1 2 3
6 8 3 8
7 5 4 5
8 4 4 9
9 1 4 10
10 6 5 6
11 3 5 9
12 7 6 7
13 6 7 6
有没有好的非递归解决方案?
编辑:试验#1:按照运行总计的思路思考,我得到了一张包含 CurrRunningTotal 和 RunningTotal 的表格,直到上一行。
WITH TE
AS (SELECT
PersonId,
FirstName,
PersonAge,
SUM(PersonAge) OVER (ORDER BY PersonId
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
AS PrevRunningTotal,
SUM(PersonAge) OVER (ORDER BY PersonId
ROWS UNBOUNDED PRECEDING)
AS RunningTotal
FROM POP),
MergedGroup
AS (SELECT
*,
SUM(CASE
WHEN RunningTotal > @total THEN RunningTotal - @total
ELSE PersonAge
END) OVER (ORDER BY PersonId) AS Total
FROM TE)
SELECT
*
FROM MergedGroup
我觉得使用 PreviousRunningTotal 当我的阈值被满足时我可以做一些魔法来获得一个带有填充的总数,即当阈值被超过时,将 10 添加到当前行以抵消总数。仍然接近但没有雪茄。
解决方案
是的,您可以轻松地满足您的条件,但不是最优的。只是分组personid
。
据推测,您的意图更像是“从头开始并使用相邻记录”或“最小化组数”。后者是一个装箱问题,并且不存在具有高效性能的已知算法。
前者——不幸的是——需要从一开始就循环遍历数据,这就是递归 CTE 所做的。我不知道解决问题的任何其他通用方法。可能有特定的方法,具体取决于您的问题的确切限制。
推荐阅读
- javascript - 如何使用数据库中的事件最好地填充日历
- python - 从大型图像数据集中删除重复项
- airflow - 在一个 dag 上多次循环执行许多气流任务
- javascript - ModuleNotFoundError:找不到模块:错误:无法解析“/vercel/workpath0/my-app/pages”中的“../components/charts/be.js”
- javascript - 在文本框中显示增值税值
- netbeans - Netbeans忽略上传相对文件路径
- docker - Docker + Asp.net core api + Apache:无限重定向循环
- python-3.x - 从数据框中获取列值的组合
- reactjs - 我正在尝试使用 useEffect' 来更改窗口。我没有收到任何错误,但它不起作用。怎么了?如何让它发挥作用?
- android - 我无法在 Visual Studio 2019 上使用 xamarin 开发的设备上部署我的 android 应用程序