我有一段代码正在寻找不同的桶数,标记的不同的桶数和未标记的桶数,到目前为止,我得到的是:
with CTE as
(select UID_KEG, IS_TAGGED, movement_date
from MOVEMENT M
inner join Keg on M.UID_Keg = Keg.Unique_ID
where DATEPART(year,Movement_date) = '2019'
and UID_MOVEMENT_TYPE = 1
)
select COUNT(Distinct CTE.UID_KEG) as 'Kegs', datepart(week,movement_date)
as 'Week number',
SUM(case when Is_Tagged = 1 then 1 end) as 'tagged',
SUM(case when Is_Tagged = 0 then 1 end) as 'untagged'
from CTE
group by datepart(week,movement_date)
order by [Week number] asc
它粗略地返回了一个不同数量的桶,但标记和未标记的数字是不正确的,我只能假设它,因为它是计数重复的桶。有谁能建议我如何绕过这个问题,或者仅仅依靠不同的小桶呢?
发布于 2019-07-01 10:47:14
您希望使用COUNT(DISTINCT)
进行条件聚合。这将是:
SELECT COUNT(DISTINCT CTE.UID_KEG) as Kegs,
datepart(week, movement_date) as Week_number,
COUNT(DISTINCT CASE WHEN Is_Tagged = 1 THEN CTE.UID_KEG END) as tagged,
COUNT(DISTINCT(CASE WHEN Is_Tagged = 0 THEN CTE.UID_KEG END) as untagged
FROM CTE
GROUP BY datepart(week, movement_date)
ORDER BY MIN(movement_date);
备注:
year()
和周,特别是因为您不是从单个年份中选择数据。发布于 2019-07-01 10:34:49
如果从计数中移除不同的值,未被挖掘和抽头的之和应该等于您的总数(如果是二进制0或1)。这表明您有重复的UID_KEG值。花点时间了解原因。部分问题是,您似乎不太了解您的数据集的形状非常好。
花点时间看看数据是否有重复(为什么?它们是由联接引起的,还是在基本数据中引起的?),请查看它们是否可以显示为标记和未标记。
编辑:回应你的评论。如果可以扫描两次,则必须假设如果Is_Tagged =1表示当天的任何UID_KEG,那么所有带有该UID_KEG的桶都会被标记。
在这种情况下,您将不得不修改代码以使用此假设。
WITH CTE
AS (
SELECT UID_KEG
,IS_TAGGED
,movement_date
FROM MOVEMENT M
INNER JOIN Keg ON M.UID_Keg = Keg.Unique_ID
WHERE DATEPART(year, Movement_date) = '2019'
AND UID_MOVEMENT_TYPE = 1
)
SELECT CTE.UID_KEG AS 'Kegs'
,datepart(week, movement_date) AS 'Week number'
,MAX(Is_Tagged) AS 'tagged'
FROM CTE
GROUP BY CTE.UID_KEG
,datepart(week, movement_date)
ORDER BY [Week number] ASC
这段代码可能不是完美的,我无法测试它,但是它应该在每一天给您一个完整的每个桶的列表,如果那个桶被标记为标记至少一次,如果它根本没有标记为标记。
这里最重要的事情是每天消除桶的重复,然后就可以计算了。
我不太擅长CTE的,但你需要聚集一个水平,直到每天的水平,现在你将能够计数不同数量的桶,哪些是被标记的和没有标记的。
希望这是合理的。
编辑:下面是一个应该工作的子查询
SELECT [Week number]
,count(1) [numKegs]
,sum(tagged) [numTagged]
FROM (
SELECT UID_KEG AS 'Kegs'
,datepart(week, movement_date) AS 'Week number'
,MAX(IS_TAGGED) AS 'tagged'
FROM MOVEMENT M
INNER JOIN Keg ON M.UID_Keg = Keg.Unique_ID
WHERE DATEPART(year, Movement_date) = '2019'
AND UID_MOVEMENT_TYPE = 1
GROUP BY UID_KEG
,datepart(week, movement_date)
) kegdailylevel
GROUP BY [Week number]
ORDER BY [Week number] ASC
https://stackoverflow.com/questions/56833441
复制相似问题