SQL Server: как подражать оракулу, держать запрос density_rank?

У меня есть запрос Oracle

select max(m.id),
       m.someId keep (DENSE_RANK FIRST ORDER BY m.UpdateDate desc) 
from MyTable m 
groupBy m.someId

который для данных, как это:

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

вернет мне именно это:

2 10
4 20
6 30

Таким образом, для каждого someId он ищет последнее updateDate и возвращает соответствующийid, (И если есть несколько идентификаторов для последних дат, требуется последний идентификатор).

Но для сервера SQL этот запрос будет работать так же? Я имею в виду эту конструкциюkeep (dense_rank first order by ..)?

Ответы на вопрос(4)

keep & quot; построить, поэтому вам нужно использовать подзапрос:

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

Это находит первую строку для каждого m.id с самой последней UpdateDate. Затем он выбирает эту строку во внешнем запросе. Обратите внимание, что с помощью этого метода вам не нужна группа.

 javagirl25 мая 2012 г., 17:43
Я не понимаю ... как это должно работать без 'group by someId'? Мой запрос всегда будет возвращать мне 3 записи, так как у меня в таблице 3 разных идентификатора. Ваш запрос будет возвращать результаты, которые не зависят от количества различных идентификаторов someIds, поэтому он должен быть неправильным?
 25 мая 2012 г., 17:43
Это не сработает, в результате у вас будет по одной строке для каждого идентификатора, а это не то, чего хочет опера.

Если у вас есть несколько заказов, вы можете сделать это (пример, сделанный на Oracle):

- этот с keep density_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

- Этот без держать density_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

существует несколько ситуаций, когда миграция использует «оконную функцию для ранжирования». стать очень сложным. Эти ситуации:

many KEEP-DENSE_RANK constructions in the select part of Oracle query based on different orders grouping by grouping sets/rollups

Поэтому я добавлю к ответу дополнительную информацию. Исходные данные SQLFIDDLE:http://sqlfiddle.com/#!6/e5c6d/6

1. Чтение функции оракула:

select max(m.id), m.someId keep (DENSE_RANK FIRST ORDER BY m.UpdateDate desc) 
from MyTable m 
groupBy m.someId

там мы выбираем максимум m.id в группе (someId, UpdateDate), где UpdateDate является самой большой в группе (someId)

2. прямой путьdoesn't work из-за ошибки:Column 'MyTable.UpdateDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT FIRST_VALUE(id) OVER(PARTITION BY someId ORDER BY UpdateDate DESC, id DESC) first_in_orderedset , someId
FROM MyTable
GROUP BY someId 

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. крест применить:

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. Теперь давайте получим более сложную таблицу и более сложный запрос: ОРАКУЛ:http://sqlfiddle.com/#!4/c943c/23 SQL 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:

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-запрос:

(to be continued)

на самом деле там я планировал поставить свой пользовательский агрегат wroted с C #. если кто-то заинтересован, пожалуйста, свяжитесь со мной ... пользовательский агрегат является лучшим решением таких проблем, но он не является универсальным с точки зрения длины varchar. для каждой длины varchar вы должны будете создать «специализированный» совокупная функция

Решение Вопроса

что ваш конкретный запрос будет запускать 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
 javagirl25 мая 2012 г., 17:44
Я не понимаю ... как это должно работать без 'group by someId'? Мой запрос всегда будет возвращать мне 3 записи, так как у меня в таблице 3 разных идентификатора. Ваш запрос будет возвращать результаты, которые не зависят от количества различных идентификаторов someIds, поэтому он должен быть неправильным?
 25 мая 2012 г., 17:59
@GordonLinoff - Да, я знаю. Вот почему у меня было это в моем ответе ;-). Кроме того, в ответе на вопрос сказано, что, когда на последнюю дату имеется несколько идентификаторов, он должен возвращать максимальный идентификатор, вы не приняли это во внимание.
 25 мая 2012 г., 17:56
Я неправильно понял исходные данные. Проблема устраняется путем помещения someid в раздел, а не в id.
 25 мая 2012 г., 17:46
@javagirl - Вы пробовали это сначала?, это будет работать. Что касается того, как это работает, аналитические функции (OVER() ...) может иметьPARTITION BY и не нужно группировать по всему уровню. Он возвращает значение для каждой строки в таблице.

Ваш ответ на вопрос