Блог

Главная - Вопросы по программированию - SQL — Выберите Рекордно Высокий уровень для каждого дня недели

SQL — Выберите Рекордно Высокий уровень для каждого дня недели

#sql #sql-server-2008

#sql #sql-server-2008

Вопрос:

Возвращает все строки, отсортированные по CountOfRecords DESC. Я бы хотел, чтобы здесь было всего семь строк, показывающих самое высокое значение за указанный день. Таким образом, каждый день недели должен наступать один и только один раз. Возможно ли это без сильной боли?

 SELECT 
    Count(*) As CountOfRecords, 
    CAST(FLOOR(CAST([visit].[datetimeentered] AS float)) AS smalldatetime) AS DateEntered, 
    DatePart(dw, visit.datetimeentered) As DayOfTheWeek
FROM visit
INNER JOIN useragent ON useragent.useragentid = visit.useragentid
WHERE useragent.isbot = 0
GROUP BY CAST(FLOOR(CAST([visit].[datetimeentered] AS float)) AS smalldatetime), 
    DatePart(dw, visit.datetimeentered)
ORDER BY CountOfRecords DESC

Правка1:
Я думаю, что оба ответа ведут к одному и тому же. Я принял тот, который был опубликован первым. Я также собираюсь упомянуть, что это сработало сразу, в то время как другой не сработал. При запуске запроса marc_s я получаю следующие ошибки:

 Msg 207, Level 16, State 1, Line 29
Invalid column name 'RowNum'.
Msg 207, Level 16, State 1, Line 25
Invalid column name 'CountOfRecords'.
Msg 207, Level 16, State 1, Line 26
Invalid column name 'DateEntered'.
Msg 207, Level 16, State 1, Line 27
Invalid column name 'DayOfTheWeek'.

Особая благодарность marc_s за указание простого способа получить только часть даты из datetime.

Комментарии:

1. Вы уже группируете данные так, как кажется правильным для вашей проблемы — есть ли причина, по которой вы не можете добавить MAX (fieldname), где fieldname — это поле, для которого вы хотите получить наибольшее доступное значение — к вашему ВЫБОРУ?

2. Какой продукт базы данных и версия? Это пахнет как SQL Server. Если да, то какая версия?

3. Я забыл упомянуть, что это SQL Server 2008. Я попробовал ваше предложение, и оно не сработало.

4. Я думаю, что должно быть разрешено принимать другой ответ вместо того, который вы приняли ранее.

5. Упс, возможно, я выбрал неправильный ответ в качестве ответа. Я думал, что сообщение Андрея отображается на 2 минуты раньше, чем сообщение marc_s.

Ответ №1:

На основе вашего запроса:

 WITH groupedByDay AS (
  SELECT 
    Count(*) As CountOfRecords, 
    CAST(FLOOR(CAST([visit].[datetimeentered] AS float)) AS smalldatetime) AS DateEntered
  FROM visit
  INNER JOIN useragent ON useragent.useragentid = visit.useragentid
  WHERE useragent.isbot = 0
  GROUP BY CAST(FLOOR(CAST([visit].[datetimeentered] AS float)) AS smalldatetime)
),
ranked AS (
  SELECT
    CountOfRecords,
    DateEntered,
    DOW = DATENAME(dw, DateEntered),
    rank = ROW_NUMBER() OVER (PARTITION BY DatePart(dw, DateEntered)
                              ORDER BY CountOfRecords DESC)
  FROM groupedByDay
)
SELECT
  CountOfRecords,
  DateEntered,
  DOW
FROM ranked
WHERE rank = 1
ORDER BY CountOfRecords DESC

Это вернет одну строку для каждого дня недели, представленного в таблице. Если среди максимальных значений могут быть дубликаты на CountOfRecords и вы хотите вернуть их все, используйте RANK() вместо ROW_NUMBER() .

Ответ №2:

На SQL Server 2005 и новее вы могли бы использовать CTE (Common Table Expression) с ROW_NUMBER() функцией ранжирования и PARTITION BY предложением — что-то вроде этого:

 ;WITH DataByDayOfWeek AS
(
   SELECT 
      Count(*) As CountOfRecords,
      CAST(FLOOR(CAST(v.[datetimeentered] AS float)) AS smalldatetime) AS DateEntered, 
      DatePart(dw, v.datetimeentered) As DayOfTheWeek
   FROM dbo.visit v
   INNER JOIN dbo.useragent u ON u.useragentid = v.useragentid
   WHERE u.isbot = 0
   GROUP BY 
       CAST(FLOOR(CAST(v.[datetimeentered] AS float)) AS smalldatetime), 
       DatePart(dw, v.datetimeentered)
),
HighestValues AS
(
    SELECT 
       CountOfRecords,
       DateEntered,
       DayOfTheWeek,
       ROW_NUMBER() OVER(PARTITION BY DayOfTheWeek 
                         ORDER BY CountOfRecords DESC) 'RowNum'
    FROM DataByDayOfWeek
)
SELECT 
   CountOfRecords,
   DateEntered,
   DayOfTheWeek
WHERE
   RowNum = 1

Позвольте мне объяснить:

  • первый CTE выполняет подсчет записей, в основном — так что вы получите по одной записи для каждой DateEntered с количеством записей и днем недели
  • второй CTE основан на первом CTE и «разделяет» ваши данные на DayOfTheWeek — таким образом, для каждого отдельного дня недели вы получаете счетчик, начинающийся с 1. Данные за каждый день недели сортируются по количеству записей в порядке убывания, поэтому наибольшее значение имеет RowNum , которое равно 1

Таким образом, выбор всех строк из второго CTE, которые имеют RowNum = 1 , дает вам наибольшее значение для каждого дня недели.

В качестве дополнительного примечания: я полагаю, что в SQL Server 2008 вам было бы гораздо проще преобразовать ваше datetime в строгое, DATE которое имеет только DATE — no time — вот так:

 CAST(v.DateTimeEntered AS DATE)

Больше никаких проблем с преобразованием в float, а затем в smalldatetime — попробуйте!