- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我正在尝试添加一个新列来计算该周每种包装类型的总百分比。
下面的代码会产生以下结果。
所以我希望新列显示该特定周百升总数的百分比。
代码:
SET DATEFIRST 1
SELECT
DATEPART (wk, t0.U_ORC_BE_ProdDate) AS [Week Produced],
--Display week number of date produced
(
CASE
WHEN t5.U_ORC_BE_NAME LIKE '%Cans%'
THEN 'Cans'
WHEN t5.U_ORC_BE_NAME LIKE '%Bottles%'
THEN 'Bottles'
WHEN t5.U_ORC_BE_NAME LIKE '%Key Keg%'
THEN 'Key Keg'
WHEN t5.U_ORC_BE_NAME LIKE '%SS Keg%'
THEN 'SS Keg'
WHEN t5.U_ORC_BE_NAME LIKE '%E-Keg%'
Then 'SS Keg'
END
)AS [Pack Type],
--Collate item types to pack types
sum(t5.U_ORC_BE_HECTOLITER * t0.CmpltQty) AS [Total Hectoliters]
--Calculate total HL in the order
FROM OWOR T0
INNER JOIN OITT T1 ON T0.ItemCode = T1.Code
INNER JOIN OITM T2 ON T1.Code = T2.ItemCode
LEFT JOIN [@ORC_BE_PACK_TYPE] t5 ON t5.Code = t2.U_ORC_BE_PACK_TYPE
LEFT JOIN [@ORC_BE_STYLE_H] t6 ON t6.Code= t2.U_ORC_BE_ShortCode
WHERE
(t5.U_ORC_BE_HECTOLITER * t0.CmpltQty) != 0
AND U_ORC_BE_ProcessType = 'Packaging'
AND
(
T0.[U_ORC_BE_ProdDate] >= dateadd(wk, datediff(wk, 0, getdate()) - 13, 0)
AND T0.[U_ORC_BE_ProdDate] < dateadd(wk, datediff(wk, 0, getdate()), 0)
)
--For previous 12 weeks
GROUP BY datepart(wk, t0.U_ORC_BE_ProdDate),
t0.U_Operator,
(
CASE
WHEN t5.U_ORC_BE_NAME LIKE '%Cans%'
THEN 'Cans'
WHEN t5.U_ORC_BE_NAME LIKE '%Bottles%'
THEN 'Bottles'
WHEN t5.U_ORC_BE_NAME LIKE '%Key Keg%'
THEN 'Key Keg'
WHEN t5.U_ORC_BE_NAME LIKE '%SS Keg%'
THEN 'SS Keg'
WHEN t5.U_ORC_BE_NAME LIKE '%E-Keg%'
Then 'SS Keg'
END
)
ORDER BY datepart (wk, t0.U_ORC_BE_ProdDate) ASC
最佳答案
使用 SUM() OVER() 来汇总每个日历单位的全部金额,并以此计算百分比。
SELECT
d.*
, ([Total Hectoliters] * 100.0) / SUM([Total Hectoliters]) OVER (PARTITION BY [Week Produced]) [Weekly Pct]
FROM (
SELECT
DATEPART(wk, t0.U_ORC_BE_ProdDate) AS [Week Produced]
--Display week number of date produced
, (
CASE
WHEN t5.U_ORC_BE_NAME LIKE '%Cans%' THEN 'Cans'
WHEN t5.U_ORC_BE_NAME LIKE '%Bottles%' THEN 'Bottles'
WHEN t5.U_ORC_BE_NAME LIKE '%Key Keg%' THEN 'Key Keg'
WHEN t5.U_ORC_BE_NAME LIKE '%SS Keg%' THEN 'SS Keg'
WHEN t5.U_ORC_BE_NAME LIKE '%E-Keg%' THEN 'SS Keg'
END
) AS [Pack Type]
--Collate item types to pack types
, SUM(t5.U_ORC_BE_HECTOLITER * t0.CmpltQty) AS [Total Hectoliters]
--Calculate total HL in the order
FROM OWOR T0
INNER JOIN OITT T1 ON T0.ItemCode = T1.Code
INNER JOIN OITM T2 ON T1.Code = T2.ItemCode
INNER JOIN [@ORC_BE_PACK_TYPE] t5 ON t5.Code = t2.U_ORC_BE_PACK_TYPE
LEFT JOIN [@ORC_BE_STYLE_H] t6 ON t6.Code = t2.U_ORC_BE_ShortCode
WHERE (t5.U_ORC_BE_HECTOLITER * t0.CmpltQty) <> 0
AND U_ORC_BE_ProcessType = 'Packaging'
--For previous 12 weeks
AND (T0.[U_ORC_BE_ProdDate] >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 13, 0)
AND T0.[U_ORC_BE_ProdDate] < DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
)
GROUP BY
DATEPART(wk, t0.U_ORC_BE_ProdDate)
, t0.U_Operator
, (
CASE
WHEN t5.U_ORC_BE_NAME LIKE '%Cans%' THEN 'Cans'
WHEN t5.U_ORC_BE_NAME LIKE '%Bottles%' THEN 'Bottles'
WHEN t5.U_ORC_BE_NAME LIKE '%Key Keg%' THEN 'Key Keg'
WHEN t5.U_ORC_BE_NAME LIKE '%SS Keg%' THEN 'SS Keg'
WHEN t5.U_ORC_BE_NAME LIKE '%E-Keg%' THEN 'SS Keg'
END
)
) AS d
ORDER BY
[Week Produced] ASC
顺便说一下,因为您的 where 子句坚持别名 t5 满足某些条件,所以在 t5 上使用左连接没有意义。也就是说,因为每一行都必须满足这些条件,所以来自 t5 的 NULL 结果将被忽略,因此外连接与该表无关。
关于sql - 占集团总数的百分比,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/38222680/
这个问题已经有答案了: How to capture an arbitrary number of groups in JavaScript Regexp? (5 个回答) 已关闭 9 年前。 我正在
我正在寻找一种方法来自动将城市中的社区定义为图形上的多边形。 我对邻里的定义有两个部分: A block :在多条街道之间封闭的区域,其中街道(边)和交叉点(节点)的数量最少为三个(三角形)。 邻域
以下是我的数据的一个最小示例: Id Session Flag toSum 0 1 1 1 2 1 1 2 0 4 2 1 3
我的应用程序中有一个 loadAlbums 方法,它使用 AssetsLibrary 的单例实例加载资源。这是到目前为止我的代码: func loadAlbums(){ let library
通常在使用 .apply() 方法时,会传递一个只接受一个参数的函数。 def somefunction(group): group['ColumnC'] == group['ColumnC'
如果这是一个基本问题,请原谅我,但我是 pandas 的新手。我有一个带有 A 列的数据框,我想根据 A 列中的计数获取前 n 行。例如,原始数据看起来像 A B C x 12 ere x 34
我是一名优秀的程序员,十分优秀!