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

6

У меня есть запрос 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   ответа
1

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. Затем он выбирает эту строку во внешнем запросе. Обратите внимание, что с помощью этого метода вам не нужна группа.

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

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

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 вы должны будете создать «специализированный» совокупная функция

1

Если у вас есть несколько заказов, вы можете сделать это (пример, сделанный на 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
8

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

Похожие вопросы