基于某些键列(即Location、Sublocation和Area),如何按这些列分组以获得标准值?在下面的示例中,唯一的规则是,按Location、Sublocation和Area分组,在分组方案的列Initial Result中,如果Excellent的值出现两次或更多次(不是多数,因为其他位置可能有更多行),则分组列的Final Result应为PASS。如果不是这样,那么FAIL。这只是更大的数据集的一部分,其中存在多个位置、子块和区域级别组合。
输入示例
Location Sublocation Initial Results
School Cafeteria Excellent
School Cafeteria Poor
School Cafeteria Excellent
School Auditorium Poor
School Auditorium Poor
School Auditorium Excellent输出
Location Sublocation Initial Results Final Results
School Cafeteria Excellent PASS
School Cafeteria Poor PASS
School Cafeteria Excellent PASS
School Auditorium Poor FAIL
School Auditorium Poor FAIL
School Auditorium Excellent FAIL我想我可以试着用上面的标准来计算一下value Excellent分组的数量。在我做完计数后,我可以简单地说如果它是2或更多,然后创建另一列并将其标记为通过或失败?我认为这需要更多的步骤。
代码尝试
select a.*, count(case when 'initial results' = 'Excellent' > 2 then 'PASS' else ' 'FAIL') end as 'Final Result'
from MyTable a
group by location, sublocation, area我收到了许多错误消息,我想这是因为我可能需要创建一个单独的计数查询?
发布于 2021-10-21 20:37:37
我的SQL有点生疏了,但是像这样的东西应该可以工作。
With cte as (
SELECT [Location],
[Sublocation],
SUM(CASE WHEN [initial results] = ‘Excellent’
THEN 1
ELSE 0
END) as cnt
FROM MyTable
GROUP BY Location, Sublocation
)
SELECT a.* ,
(CASE WHEN c.cnt > 2
THEN ‘PASS’
ELSE ‘FAIL’
END) as [Final Results]
FROM cte c
RIGHT JOIN MyTable a ON (c.Location = a.Location AND c.Sublocation = a.Sublocation)基本上使用一个公用表表达式来求和,其中“初始结果”等于“优秀”。然后连接回主表并使用用例逻辑来查找哪里‘优秀’>2
https://stackoverflow.com/questions/69666719
复制相似问题