oracle SQL Server：如何模仿oracle保持dense_rank查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10756717/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: how to imitate oracle keep dense_rank query?
提问by javagirl
I have an Oracle query
我有一个 Oracle 查询
select max(m.id),
m.someId keep (DENSE_RANK FIRST ORDER BY m.UpdateDate desc)
from MyTable m
groupBy m.someId
which for data like this:
对于这样的数据：
id UpdateDate someId
1 20-01-2012 10
2 20-01-2012 10
3 01-01-2012 10
4 10-02-2012 20
5 01-02-2012 20
6 01-04-2012 30
will return me exactly this:
将准确地返回给我：
2 10
4 20
6 30
So, for every someId it searches for latest updateDate and does return the appropriate id. (And if there are a several ids for the latest dates it takes latest id).
因此，对于每个 someId，它都会搜索最新的 updateDate 并返回适当的id. （如果最新日期有多个 id，则需要最新的 id）。
But for SQL server will this query work the same way? I mean this construction keep (dense_rank first order by ..)?
但是对于 SQL Server，这个查询会以同样的方式工作吗？我是说这个建筑keep (dense_rank first order by ..)？
回答by Lamak
I don't think that your particular query will run SQL Server. But you can achieve the same result doing this:
我认为您的特定查询不会运行 SQL Server。但是您可以通过以下方式获得相同的结果：
SELECT id, SomeId
FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY someId ORDER BY UpdateDate DESC, id DESC) Corr
FROM MyTable) A
WHERE Corr = 1
回答by Roman Pokrovskij
I return and return to this question and the answer. Unfortunately there are several situations when migration using the "window function for ranking" become very complex. Those situations are:
我回到这个问题和答案。不幸的是，当使用“用于排名的窗口函数”的迁移变得非常复杂时，有几种情况。这些情况是：
- many KEEP-DENSE_RANK constructions in the select part of Oracle query based on different orders
- grouping by grouping sets/rollups
- Oracle查询的select部分中的很多KEEP-DENSE_RANK构造基于不同的顺序
- 通过分组集/汇总分组
Therefore I will add to the answer additional information. Original data SQLFIDDLE: http://sqlfiddle.com/#!6/e5c6d/6
因此，我将在答案中添加其他信息。原始数据 SQLFIDDLE：http://sqlfiddle.com/#!6/e5c6d/6
1.Reading oracle function:
1.阅读oracle功能：
select max(m.id), m.someId keep (DENSE_RANK FIRST ORDER BY m.UpdateDate desc)
from MyTable m
groupBy m.someId
there we select max of m.id in the group (someId, UpdateDate) where UpdateDate is biggest it the group (someId)
我们在组中选择 m.id 的最大值 (someId, UpdateDate)，其中 UpdateDate 是组中最大的 (someId)
2.straight forward way doesn't workbecause of error: Column 'MyTable.UpdateDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
2.由于错误，直接方法不起作用：列“MyTable.UpdateDate”在选择列表中无效，因为它既不包含在聚合函数中也不包含在 GROUP BY 子句中。
SELECT FIRST_VALUE(id) OVER(PARTITION BY someId ORDER BY UpdateDate DESC, id DESC) first_in_orderedset , someId
FROM MyTable
GROUP BY someId
3.improoved 'straight forward' is noneffective
3.改进的“直截了当”无效
SELECT someId, MIN(first_in_orderedset)
FROM
( SELECT FIRST_VALUE(id) OVER(PARTITION BY someId ORDER BY UpdateDate DESC, id DESC) first_in_orderedset , someId
FROM MyTable ) t
GROUP BY someId;
4.cross apply:
4.交叉申请：
SELECT grouped.someId, orderedSet.FirstUpdateDate, maxInSet.first_in_orderedset FROM
(
SELECT mt.someId
FROM MyTable mt
GROUP BY mt.someId
) grouped CROSS APPLY
(
SELECT top 1 mt2.UpdateDate as FirstUpdateDate
FROM MyTable mt2
WHERE mt2.someId=grouped.someId
ORDER BY UpdateDate desc
) orderedSet CROSS APPLY
(
SELECT max(mt3.id) as first_in_orderedset
FROM MyTable mt3
WHERE mt3.someId=grouped.someId and mt3.UpdateDate=orderedSet.FirstUpdateDate
) maxInSet;
5.Now lets get the more complex table and more complex query: ORACLE : http://sqlfiddle.com/#!4/c943c/23SQL SERVER: http://sqlfiddle.com/#!6/dc7fb/1/0(data is pregenerated and it is the same in both sandboxes - it is easy to compare results) Table:
5.现在让我们得到更复杂的表和更复杂的查询： ORACLE : http://sqlfiddle.com/#!4/c943c/23SQL SERVER: http://sqlfiddle.com/#!6/dc7fb/1/ 0（数据是预先生成的并且在两个沙箱中是相同的 - 很容易比较结果）表：
CREATE TABLE AlarmReports (
id int PRIMARY KEY,
clientId int, businessAreaId int , projectId int, taskId int,
process1Spent int, process1Lag int, process1AlarmRate varchar2(1) null,
process2Spent int, process2Lag int, process2AlarmRate varchar2(1) null,
process3Spent int, process3Lag int, process3AlarmRate varchar2(1) null
)
Oracle query:
甲骨文查询：
SELECT clientId, businessAreaId, projectId,
sum(process1Spent),
sum(process2Spent),
sum(process3Spent),
MIN(process1AlarmRate) KEEP (DENSE_RANK FIRST ORDER BY process1Lag DESC),
MIN(process2AlarmRate) KEEP (DENSE_RANK FIRST ORDER BY process2Lag DESC),
MIN(process3AlarmRate) KEEP (DENSE_RANK FIRST ORDER BY process3Lag DESC)
FROM AlarmReports
GROUP BY GROUPING SETS ((),(clientId),(clientId, projectId),(businessAreaId),(clientId,businessAreaId))
SQL query:
SQL查询：
(to be continued)
actually there I have planned to put my custom aggregate wroted with c#. if somebody are interested, please contact me... custom aggregate is the best solution of such problems but it is not unviersal in terms of varchar lengths. for each varchar length you would be obligated to create "specialised" aggreate function
实际上，我计划将我用 c# 编写的自定义聚合放在那里。如果有人感兴趣，请与我联系...自定义聚合是此类问题的最佳解决方案，但它在 varchar 长度方面并非通用。对于每个 varchar 长度，您将有义务创建“专门的”聚合函数
回答by ?ilvinas
That will work absolutely. Try first, then argue. When you have multiple order by you can do this(example made on Oracle):
那绝对行得通。先尝试，再争论。当您有多个订单时，您可以这样做（在 Oracle 上制作的示例）：
-- this one with keep dense_rank
-- 这个有保持dense_rank的
WITH a AS (SELECT 1 s1, 4 s2, 'a' c, 10 g FROM dual UNION all
SELECT 2 s1, 2 s2, 'b' c, 10 g FROM dual UNION ALL
SELECT 3 s1, 1 s2, 'c' c, 20 g FROM dual UNION ALL
SELECT 4 s1, 3 s2, 'd' c, 20 g FROM dual)
SELECT g,
MAX(c) KEEP (DENSE_RANK FIRST ORDER BY s1) s1,
MAX(c) KEEP (DENSE_RANK FIRST ORDER BY s2) s2
FROM a
GROUP BY g
-- This one without keep dense_rank
-- 这个没有保持dense_rank
WITH a AS (SELECT 1 s1, 4 s2, 'a' c, 10 g FROM dual UNION all
SELECT 2 s1, 2 s2, 'b' c, 10 g FROM dual UNION ALL
SELECT 3 s1, 1 s2, 'c' c, 20 g FROM dual UNION ALL
SELECT 4 s1, 3 s2, 'd' c, 20 g FROM dual)
SELECT g,
MAX(DECODE(s1, 1, c)) s1,
MAX(DECODE(s2, 1, c)) s2
FROM (SELECT g,c,
ROW_NUMBER() OVER (PARTITION BY g ORDER BY s1) s1,
ROW_NUMBER() OVER (PARTITION BY g ORDER BY s2) s2
FROM a) b
GROUP BY g
回答by Gordon Linoff
SQL Server does not support the "keep" construct, so you need to use a subquery:
SQL Server 不支持“保持”构造，因此您需要使用子查询：
select m.*
from (select *, row_number() over (partition by m.someid ORDER BY m.UpdateDate desc) as seqnum
from MyTable m
) m
where seqnum = 1
This finds the first row for each m.id with the most recent UpdateDate. It then chooses that row in the outer query. Note that you don't need a group by with this method.
这会为每个 m.id 找到具有最新更新日期的第一行。然后它在外部查询中选择该行。请注意，使用此方法不需要 group by。
回答by Michael Buen
In case someone look for Oracle KEEP DENSE_RANK simulation in Postgres:
如果有人在 Postgres 中寻找 Oracle KEEP DENSE_RANK 模拟：
CREATE TABLE myt (
"id" INTEGER,
"update_date" timestamp,
"some_id" INTEGER
);
INSERT INTO myt
("id", "update_date", "some_id")
VALUES
('1', '2012-01-20', '10'),
('2', '2012-01-20', '10'),
('3', '2012-01-01', '10'),
('4', '2012-10-02', '20'),
('5', '2012-01-02', '20'),
('6', '2012-01-04', '30');
select
some_id,
(array_agg(id order by update_date desc, id desc))[1]
from myt
group by some_id
order by some_id
