SQL-如何在列中获取唯一值(此处不起作用) [英] SQL - how to get unique values in a column (distinct does not help here)
问题描述
我有一个生产案例,我们跟踪在仓库中移动的设备。我有一张表格,显示了这些以前的位置,例如:
I have a production case where we track devices that move around warehouses. I have a table that shows these previous locations like this:
+--------+------------+-----------+------+
| device | current_WH | date | rank |
+--------+------------+-----------+------+
| 1 | AB | 3/15/2018 | 7 |
| 1 | CC | 3/19/2018 | 6 |
| 1 | CC | 3/22/2018 | 5 |
| 1 | CC | 3/22/2018 | 5 |
| 1 | DD | 4/23/2018 | 4 |
| 1 | DD | 5/11/2018 | 3 |
| 1 | DD | 5/15/2018 | 2 |
| 1 | DD | 5/15/2018 | 2 |
| 1 | AA | 6/6/2018 | 1 |
| 1 | AA | 6/6/2018 | 1 |
+--------+------------+-----------+------+
但是我需要找到current_WH的唯一值并将设备减少到一行(有数百万个设备),像这样:
But I need to find those unique values of current_WH and reduce the device to one line (there are millions of devices), like this:
+--------+------------+--------+--------+--------+
| device | current_WH | prev_1 | prev_2 | prev_3 |
+--------+------------+--------+--------+--------+
| 1 | AA | DD | CC | AB |
+--------+------------+--------+--------+--------+
我使用了等级函数(按日期按设备顺序分组)。它几乎做到了,但不完全是。您无法对current_WH进行排名,因为它将按字母顺序排名。我需要按时间对current_WH进行排名。
I used the rank function (group by device order by date). It almost does it but not quite. You can't rank current_WH because that will rank alphabetically. I need to rank current_WH by time.
有人知道如何实现第二张桌子吗?谢谢你的帮助!
Any idea how to achieve the second table? Thanks for your help!
推荐答案
我认为这符合您的要求:
I think this does what you want:
select device,
max(case when seqnum = 1 then current_wh end) as current_wh,
max(case when seqnum = 2 then current_wh end) as prev_1_wh,
max(case when seqnum = 3 then current_wh end) as prev_2_wh,
max(case when seqnum = 4 then current_wh end) as prev_3_wh
from (select t.*, dense_rank() over (partition by device order by maxdate desc) as seqnum
from (select t.*, max(date) over (partition by device, current_wh) as maxdate
from t
) t
) t
group by device
这篇关于SQL-如何在列中获取唯一值(此处不起作用)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!