Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Выборка определённого количества строк на каждую группу  [new]
Arabezar
Member

Откуда: Москва
Сообщений: 14
Привет всем!

Чё-то запарился с задачей...
Есть очччень большая таблица (любой SELECT ... GROUP BY ... отрабатывает полчаса).
Хочу для тестирования урезать эту таблицу до разумных размеров, например, оставив по 5 строк на каждую дату.
Подскажите, плиз, как это сделать, чтобы запрос отработал быстрее?

Итак, есть таблица дат (даже так :))

TABLE table_dates (
  [the_date] [datetime] NOT NULL
)

и ОЧЧЧЕНЬ большая таблица :) :
TABLE table_data (
  [the_date] [datetime] NOT NULL,
  [qty] [decimal](18, 0) NULL
)

1. Надо в таблице table_data оставить по 5 строк на каждую дату (можно с использованием таблицы table_dates или без неё).
2. Процедура должна работать максимально быстро. Подозреваю, что конструкции WHILE... (SELECT...) не подходят.

Буду очень признателен за любые подсказки.
28 дек 09, 19:01    [8129120]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
коля123456
Guest
ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ... ) вам в помощь
28 дек 09, 20:07    [8129275]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
Arabezar
Member

Откуда: Москва
Сообщений: 14
Подскажите, плиз, так верно?

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER(ORDER BY the_date) AS Num
  FROM table_data) AS data
WHERE Num <= 5
28 дек 09, 22:13    [8129574]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3217
Из очень большой кучи никакой селект не будет быстрым, а такой - в особенности.

У вас там хоть первичный ключ есть? Индексы какие-нибудь?..
28 дек 09, 22:41    [8129638]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
Ozzy-Osbourne
Member

Откуда: Balashikha
Сообщений: 139
Arabezar,

создайте в своей большой таблице индекс по the_date и дальше почитайте вот это (там очень похожая ситуация: "для каждого элемента родительской таблицы вытащить 2 (можно N) последних по дате элементов дочерней").
29 дек 09, 00:38    [8129810]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
коля123456
Guest
PARTITION BY упустили из виду

соответственно ваш запрос получится таким

SELECT * FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY cast(the_date as date) ORDER BY the_date) AS Num
FROM table_data) AS data
WHERE Num <= 5

Запрос работать будет долго. но так как нужно построить табличку для тестовых целей
- соответственно запрос будет выполнятс один раз. А всего лишь один раз и подождать можно...

И еще cast(the_date as date) - точно работает в 2008 на счет 2005 и ранее не уверен.
29 дек 09, 10:24    [8130560]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
mike909
Member

Откуда:
Сообщений: 662
Arabezar,

declare @StartDate datetime, @NumOfDay int
set @NumOfDay = 10

set @StartDate = DateAdd(d, -@NumOfDay, cast(convert(nvarchar(8), GetDate(), 112) as datetime))

;with L0 as (select 0 as N union all select 0),
     L1 as (select 0 as N from L0 t1 cross join L0 t2),
     L2 as (select 0 as N from L1 t1 cross join L1 t2),
     L3 as (select 0 as N from L2 t1 cross join L2 t2),
     L4 as (select row_number() over(order by t1.N) N from L3 t1 cross join L3 t2)
select BigTbl.*
from (select top(@NumOfDay) * from L4) as n
cross apply( select dateadd(d, n.n-1, @StartDate) as [b], dateadd(d, n.n, @StartDate) as [e] ) as dt
cross apply(
  select top 5 *
  from table_data
  where [the_date] between dt.[b] and dt.[e]
) as BigTbl

Если у Вас под "датой" подразумевается не день а еще что-то, то замените "cross apply" на свое...
29 дек 09, 11:18    [8130975]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
Arabezar
Member

Откуда: Москва
Сообщений: 14
Ennor Tiegael
Из очень большой кучи никакой селект не будет быстрым, а такой - в особенности.
У вас там хоть первичный ключ есть? Индексы какие-нибудь?..

Это понятно, что селект не будет быстрым, я всего лишь хочу, чтобы он был один... и не в цикле... :)
Да, первичный ключ есть, индексы есть. Все поля я не стал вам рассказывать, ибо здесь они не нужны :)

Ozzy-Osbourne
создайте в своей большой таблице индекс по the_date и дальше почитайте вот это (там очень похожая ситуация: "для каждого элемента родительской таблицы вытащить 2 (можно N) последних по дате элементов дочерней").

Индекс есть, спасибо за ссылку, пойду читать...

коля123456
PARTITION BY упустили из виду

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY cast(the_date as date) ORDER BY the_date) AS Num
  FROM table_data) AS data
WHERE Num <= 5

Запрос работать будет долго. но так как нужно построить табличку для тестовых целей
- соответственно запрос будет выполнятс один раз. А всего лишь один раз и подождать можно...

PARTITION BY особо мне не нужен, мне интересна производительность, но не до фанатизма :) По-любому, спасибо.
Запрос будет работать долго - знаю, я готов. Да, Вы правы, один раз всего :)

Теперь о главном... Вчера вечером запустил запрос:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER(ORDER BY the_date) AS Num
  FROM table_data) AS data
WHERE Num <= 5

... он выдал мне ровно 5 строк... ((((((
Но мне надо ПО 5 СТРОК НА КАЖДУЮ ДАТУ!!!
Есть ещё идеи?
29 дек 09, 17:38    [8134362]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
коля123456
Guest
а что тот запрос который я вам поправил отдает больше чем 5 записей за одну дату?
29 дек 09, 17:45    [8134433]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Arabezar
Теперь о главном... Вчера вечером запустил запрос:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER(ORDER BY the_date) AS Num
  FROM table_data) AS data
WHERE Num <= 5

... он выдал мне ровно 5 строк... ((((((
Но мне надо ПО 5 СТРОК НА КАЖДУЮ ДАТУ!!!
Есть ещё идеи?
Но ведь сказали же уже - PARTITION BY!!!
29 дек 09, 17:48    [8134456]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
Arabezar
Member

Откуда: Москва
Сообщений: 14
mike909,

Вот это жесть... Ниасилил... Я не силён в SQL, мне ОЧЧЕНЬ сложно разобраться в вашей процедуре...
Понял одно - это суперуниверсальная процедура, которая принимает на вход дату и кол-во дней и в БОЛЬШОЙ таблице оставляет по 5 строк на каждую из дат вышеупомянутого диапазона...
Круто, одним словом. Но использовать я её не могу в силу своей сложности :(, ибо я просто не понимаю, что она делает... :(

Хотелось бы попроще запрос... и никакой универсальности. Надо просто оставить по 5 строк в БОЛЬШОЙ таблице на каждую дату (из неё же, никаких джойнов к таблице дат, так проще).
29 дек 09, 18:01    [8134550]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
Arabezar
Member

Откуда: Москва
Сообщений: 14
iap
Но ведь сказали же уже - PARTITION BY!!!

Вот здесь отмечу БОЛЬШОЕ списибо за разъяснения :), ибо я как раз и не знал, что здесь собака роется... )))
ща запущу...
29 дек 09, 18:07    [8134581]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
Arabezar
Member

Откуда: Москва
Сообщений: 14
коля123456
а что тот запрос который я вам поправил отдает больше чем 5 записей за одну дату?

Громадное спасибо, ща пускану...
29 дек 09, 18:08    [8134584]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
Arabezar
Member

Откуда: Москва
Сообщений: 14
коля123456,

Ещё раз, БОЛЬШОЕ СПАСИБО! Работает :)

INSERT INTO table_data_2
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY cast(the_date as datetime) ORDER BY the_date) AS Num
  FROM table_data) AS data
WHERE Num <= 5
29 дек 09, 20:20    [8134890]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
Arabezar
Member

Откуда: Москва
Сообщений: 14
А теперь усложним задачу... так просто, ради тренировки ума...
Записи надо не выбирать из таблицы (я выбираю, записываю в соседнюю таблицу, потом стираю первую, переписываю из второй в первую), а оставлять...
Итак, как в таблице оставить(!) по 5 строк на каждую дату? Остальные (лишние) записи удалить.
29 дек 09, 20:50    [8134944]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Arabezar
А теперь усложним задачу... так просто, ради тренировки ума...
Записи надо не выбирать из таблицы (я выбираю, записываю в соседнюю таблицу, потом стираю первую, переписываю из второй в первую), а оставлять...
Итак, как в таблице оставить(!) по 5 строк на каждую дату? Остальные (лишние) записи удалить.
А своё-то решение хоть раз покажете? Потребительское отношение к сообществу, не находите?
WITH CTE(N) AS
(
 SELECT ROW_NUMBER()OVER(PARTITION BY DATEADD(DAY, DATEDIFF(DAY,0,the_date),0) ORDER BY the_date)
 FROM table_data
)
DELETE CTE WHERE N>5;
29 дек 09, 21:43    [8135010]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Можно чуть проще:
WITH CTE(N) AS
(
 SELECT ROW_NUMBER()OVER(PARTITION BY DATEDIFF(DAY,0,the_date) ORDER BY the_date)
 FROM table_data
)
DELETE CTE WHERE N>5;
29 дек 09, 21:53    [8135022]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
Arabezar
Member

Откуда: Москва
Сообщений: 14
iap
А своё-то решение хоть раз покажете? Потребительское отношение к сообществу, не находите?

Ни в коем случае не потребительское. Я высоко ценю оказанную мне здесь помощь, не часто это встретишь. Чтобы не показаться исключительно потребителем готов попытаться помочь в C/C++/X++.
Собственно, решение, которое я использовал - это ваше решение :), я его указал в своем последнем посте. Другое дело, что я не сразу удалял записи из таблицы, а сначала копировал по 5 строк на дату в таблицу-зеркало, потом стирал исходную таблицу и, наконец, переносил созданные строки назад, в исходную таблицу, хотя можно было всего-лишь воспользоваться Вашим последним скриптом, без каких-либо изворотов.
30 дек 09, 14:04    [8137514]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Arabezar
Собственно, решение, которое я использовал - это ваше решение :), я его указал в своем последнем посте.
Во-первых, мой первый скрипт в этой теме касается удаления. Так что Вы что-то путаете
Во-вторых, в этом Вашем последнем посте написано нечто странное:
cast(the_date as datetime)
А надо-то было откусить в datetime время, верно?
Как же это может работать?

P.S. Тот факт, что рекомендованное Вам преобразование к типу DATE Вы заменили на "преобразование"
к типу DATETIME, косвенно указывает, что у Вас SQL2005
30 дек 09, 14:23    [8137640]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
mike909
Member

Откуда:
Сообщений: 662
iap,

А чем это мой запрос не понравился ?
Примерно 7 миллиардов записей обработал за 351 милисекунду. Индекс по DateTime имеется...
Да и грохнуть оставшиеся записи проще через select * into TemTbl ... drop table OldTbl ... sp_reneame ...
30 дек 09, 17:50    [8138844]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
mike909
iap,

А чем это мой запрос не понравился ?
Примерно 7 миллиардов записей обработал за 351 милисекунду. Индекс по DateTime имеется...
Да и грохнуть оставшиеся записи проще через select * into TemTbl ... drop table OldTbl ... sp_reneame ...
Не знаю, о чём Вы. Я только сказал своё мнение об этом (обратите внимание на выражение в PARTITION BY)
30 дек 09, 19:48    [8139195]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
Arabezar
Member

Откуда: Москва
Сообщений: 14
iap
Во-первых, мой первый скрипт в этой теме касается удаления. Так что Вы что-то путаете

Слово «ваше» адресовано форуму, а не лично, иначе я бы написал «Ваше» :)
Если быть конкретнее, то сначала я использовал решение коля123456.

iap
Во-вторых, в этом Вашем последнем посте написано нечто странное:
cast(the_date as datetime)
А надо-то было откусить в datetime время, верно?
Как же это может работать?

P.S. Тот факт, что рекомендованное Вам преобразование к типу DATE Вы заменили на "преобразование"
к типу DATETIME, косвенно указывает, что у Вас SQL2005

Нет, не надо ничего откусывать. Надо было из всех данных таблицы оставить только по 5 строк на каждую дату. Но, к сожалению, у меня SQL 2005, Вы правы.

iap
WITH CTE(N) AS
(
 SELECT ROW_NUMBER()OVER(PARTITION BY DATEDIFF(DAY,0,the_date) ORDER BY the_date)
 FROM table_data
)
DELETE CTE WHERE N>5;

Ваш запрос отработал за 7.5 часов и дал те же результаты. Мне он (запрос) нравится больше всего :), т.к. не надо ничего перекидывать между таблицами в последствии... :)
ГРОМАДНОЕ СПАСИБО ещё раз!
30 дек 09, 20:23    [8139286]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
Arabezar
Member

Откуда: Москва
Сообщений: 14
mike909
iap,

А чем это мой запрос не понравился ?
Примерно 7 миллиардов записей обработал за 351 милисекунду. Индекс по DateTime имеется...
Да и грохнуть оставшиеся записи проще через select * into TemTbl ... drop table OldTbl ... sp_reneame ...

Наверное, это мне не понравился, а не iap. Я уже объяснил, что он слишком сложный для меня. Ниасилил я его... :) По-любому, спасибо за помощь!
К тому же, большая таблица состоит не из двух полей, я написал это для упрощения, на самом деле там 17 колонок - около 500 байт на запись.
30 дек 09, 20:27    [8139303]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
mike909
Member

Откуда:
Сообщений: 662
Arabezar,
Ну так что мешает просто попробовать запустить мой запрос. Ждать результата придется не 7.5 часов а пару микросекунд ... Да и кол-во полей для моего запроса фиолетово ...
А пояснить запросец можно:
Первая часть
;with L0 as (select 0 as N union all select 0),
     L1 as (select 0 as N from L0 t1 cross join L0 t2),
     L2 as (select 0 as N from L1 t1 cross join L1 t2),
     L3 as (select 0 as N from L2 t1 cross join L2 t2),
     L4 as (select row_number() over(order by t1.N) N from L3 t1 cross join L3 t2)
select *
from (select top(@NumOfDay) * from L4) as n
Просто выведет последовательность 1,2,3...@NumOfDay
Для каждого числа из этой последовательности получаем диапазон времени одного дня начиная с@StartDate
cross apply( select dateadd(d, n.n-1, @StartDate) as [b], dateadd(d, n.n, @StartDate) as [e] ) as dt
Ну и в заключении вытаскиваем первые попавшиеся 5 записей из этого диапазона
cross apply(
  select top 5 *
  from table_data
  where [the_date] between dt.[b] and dt.[e]
) as BigTbl
Просто как грабли ...
Т.к. над большой таблицей никаких сортировок не проводится, то, при наличии индекса по полю [the_date] выборка 5 записей пройдет мухой.
А то, что предлагал iap приведет к сортировке всей таблицы даже при наличии индекса.
30 дек 09, 20:44    [8139356]     Ответить | Цитировать Сообщить модератору
 Re: Выборка определённого количества строк на каждую группу  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
mike909
А то, что предлагал iap приведет к сортировке всей таблицы даже при наличии индекса.
Я отвечал только на вопрос об удалении. Где Ваш скрипт на удаление?
Может, в таблице 2 миллиарда записей, откуда я знаю?
Если это так, то просто удалить достаточно большую часть таблицы займёт много времени.
Даже без отбора записей по каждому часу. Кстати, про индексы автор что-нибудь говорил? Не помню...

Arabezar,
Вы так и не поняли, что для группировки по суткам надо обнулить время в the_date?
Для этого коля123456 предложил Вам преобразовать тип этого поля к DATE с оговоркой, что это будет работать только в SQL2008.
Поэтому я Вам написал группировку (PARTITION BY) по-другому (DATEDIFF()).
30 дек 09, 21:23    [8139455]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить