SQL:添加逻辑以在选择最大值时处理平局

Question

我有一个这样的表：

类型<1><1>

存储		货架	计数
A	s1	t1	3
A	s1	t2	4
A	s2	t3	2
A	s2	t2	2
A	s3	t4
A	s3	t5

您可以使用窗口函数。想法是：

• 按货架和商店对具有type的行进行计数
• 通过计数来枚举货架内和商店内的计数，这样您就知道哪个计数最高
• 检查机框是否只有一种类型。如果是，请使用它
• 检查一个商店是否只有一种类型。如果是，请使用它
• 否则，'can''t decide'

作为一个查询，这看起来像：

select t.*,
(case when min(case when seqnum_shelf = 1 then type end) over (partition by store, shelf) =
max(case when seqnum_shelf = 1 then type end) over (partition by store, shelf) =
then min(case when seqnum_shelf = 1 then type end) over (partition by store, shelf)
when min(case when seqnum_store = 1 then type end) over (partition by store) =
max(case when seqnum_store = 1 then type end) over (partition by store) =
then min(case when seqnum_store = 1 then type end) over (partition by store)
else 'can''t decide'
end) as dominantType                   
from (select t.*,
dense_rank() over (partition by store, shelf order by cnt_shelf desc) as seqnum_shelf,
dense_rank() over (partition by store order by cnt_shelf desc) as seqnum_store
from (select t.*,
count(*) over (partition by store, shelf, type) as cnt_shelf,
count(*) over (partition by store, type) as cnt_store
from t
) t;