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

Откуда:
Сообщений: 8
Доброго времени суток вам! Есть таблица:

Дата новой цены Цена Товар
2007-02-12 00:00:00.000 600.50 10
2008-02-16 00:00:00.000 610.20 10
2009-03-05 00:00:00.000 580.45 10
2009-05-20 00:00:00.001 575.10 10
2009-01-11 00:00:00.002 825.45 11
2009-02-16 00:00:00.003 820.45 11
2009-04-18 00:00:00.000 801.00 11
2009-02-16 00:00:00.001 300.25 15
2007-10-20 00:00:00.002 504.17 16
2008-05-15 00:00:00.003 505.20 16
2009-03-05 00:00:00.000 500.10 16
2009-05-20 00:00:00.000 488.15 16

Есть ли возможность по каждому виду товара в группе на каждую дату новой цены находить разницу с ценой предыдущей даты новой цены? Спасибо большое за все ответы заранее!
29 май 09, 08:52    [7242516]     Ответить | Цитировать Сообщить модератору
 Re: Предыдущая запись в группе.  [new]
iap
Member

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

хотелось бы знать:

  • версию сервера (это очень важно)
  • структуру таблицы с констрейнтами и уникальными индексами (в виде команды CREATE TABLE)

    P.S. Пользуйтесь тегами [SRС], [FIХ] и т.п. пожалуйста.
  • 29 май 09, 08:58    [7242526]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    NewcomerSQL
    Member

    Откуда:
    Сообщений: 8
    MS SQL Serv 2000.
    А табличка очень проста:

    CREATE TABLE [Prices](
    [Row_ID] [numeric](18, 4) NULL,
    [DATE_IZM] [datetime] NOT NULL,
    [VALUE] [numeric](18, 2) NULL,
    [ID_Nomenkl] [char](15) COLLATE Cyrillic_General_CI_AS NULL
    ) ON [PRIMARY]

    где
    [DATE_IZM] - Дата новой цены,
    [VALUE] - Цена,
    [ID_Nomenkl] - товар.
    29 май 09, 09:15    [7242556]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    aleks2
    Guest
    NewcomerSQL,
    Можно, НО если у вас ожидаются миллионы записей будет не айс...

    Измените таблицу, добавте ссылку на ПРЕДЫДУЩИЙ и поддерживайте актуальность триггером.
    29 май 09, 10:02    [7242735]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 46975
    aleks2
    Можно
    А я думаю, что для такой структуры, как раз-таки, нельзя.
    Ведь дата неуникальна, для "последней даты" вполне может найтись более одной записи, а уникального ключа вообще нет...

    А так-то, конечно, коррелированным подзапросом, например, можно было бы реализовать.
    Или джойном с самой собой, опять-таки, по дате.

    Про быстродействие согласен, не айс.
    29 май 09, 10:16    [7242790]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    NewcomerSQL
    Member

    Откуда:
    Сообщений: 8
    Простите пожалуйста, а где добавляется "Ссылка на предыдущий"?
    29 май 09, 10:21    [7242813]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    aleks2
    Guest
    NewcomerSQL
    Простите пожалуйста, а где добавляется "Ссылка на предыдущий"?


    Вестимо в таблице. Где ж еще?

    iap
    А я думаю, что для такой структуры, как раз-таки, нельзя.
    Ведь дата неуникальна,

    Ну... вроде какой-то row_id там присутствует...
    29 май 09, 10:29    [7242850]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    NewcomerSQL
    Member

    Откуда:
    Сообщений: 8
    В таблице-то понятно, а вот каким образом...
    29 май 09, 10:35    [7242890]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104760
    NewcomerSQL
    В таблице-то понятно, а вот каким образом...

    Таким образом, что при занесении новой записи для нее определяется предыдущая. И эта информация и записывается в таблицу
    29 май 09, 10:37    [7242901]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    NewcomerSQL
    Member

    Откуда:
    Сообщений: 8
    Суть очень ясна. Я ее и излагала с самого начала. Не знаю механизмов исполнения.
    29 май 09, 10:43    [7242945]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104760
    NewcomerSQL
    Суть очень ясна. Я ее и излагала с самого начала. Не знаю механизмов исполнения.

    Вы изначально говорите про уже заполненную таблицу
    А вам говорят про процесс ее заполнения
    Т.е. у вас изначально должен быть столбец "Предыдущая запись", куда команда INSERT должна сразу заносить данные.
    29 май 09, 11:01    [7243065]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    NewcomerSQL
    Member

    Откуда:
    Сообщений: 8
    А таблица мне копируется целиком. Т.е., о процессе заполнения речи нет.
    Хорошо, т.е., считывать в курсор предыдущую запись, затем заполнять ею дополнительный столбец, так? Или есть какая-то хитрость типа previos(), которая выбирает по группе max дату, меньшую, чем последняя max дата (индексы можно сделать) в группе данного товара?
    29 май 09, 11:12    [7243120]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    iap
    Member

    Откуда: Москва
    Сообщений: 46975
    aleks2
    iap
    А я думаю, что для такой структуры, как раз-таки, нельзя.
    Ведь дата неуникальна,

    Ну... вроде какой-то row_id там присутствует...
    Да. Действительно.
    Но я не случайно просил расссказать про уникальные индексы.
    Ведь без этого всё держится только на честном слове...
    29 май 09, 11:19    [7243167]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    aleks2
    Guest
    NewcomerSQL
    А таблица мне копируется целиком. Т.е., о процессе заполнения речи нет.
    Хорошо, т.е., считывать в курсор предыдущую запись, затем заполнять ею дополнительный столбец, так? Или есть какая-то хитрость типа previos(), которая выбирает по группе max дату, меньшую, чем последняя max дата (индексы можно сделать) в группе данного товара?


    Если таблица вам копируется, то копируйте в таблицу с identity упорядоченно.

    insert ...
    select ...
    from
    ...
    ORDER BY Товар, [Дата новой цены] DESC


    После чего предыдущая запись = записи с ID-1 для того же товара.
    29 май 09, 11:24    [7243196]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    iljy
    Guest
    aleks2,
    Вообще говоря запрос можно построить так:

    
    SELECT [Дата новой цены], [Цена], [Товар], 
         (SELECT TOP 1 [Цена] FROM Table t2 WHERE t2.[Дата новой цены] < t1.[Дата новой цены]
          ORDER BY [[Дата новой цены]]) as PrevPrice
    FROM Table t1
    

    планировщик обычно с такими справляется, если индексы есть.
    если нужна еще и предыдущая дата - будет сложнее, посколько 2000, но тоже можно.
    29 май 09, 11:45    [7243354]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    NewcomerSQL
    Member

    Откуда:
    Сообщений: 8
    Спасибо большое за идеи! Сейчас попробую все варианты с identity.
    29 май 09, 12:19    [7243589]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    aleks2
    Guest
    iljy
    aleks2,
    Вообще говоря запрос можно построить так:

    
    SELECT [Дата новой цены], [Цена], [Товар], 
         (SELECT TOP 1 [Цена] FROM Table t2 WHERE t2.[Дата новой цены] < t1.[Дата новой цены]
          ORDER BY [[Дата новой цены]]) as PrevPrice
    FROM Table t1
    

    планировщик обычно с такими справляется, если индексы есть.
    если нужна еще и предыдущая дата - будет сложнее, посколько 2000, но тоже можно.

    Спасиба, канешно, только я в курсе этой элементарщины. Проблемы начинаются когда много записей...
    29 май 09, 12:23    [7243613]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    гм-гм
    Guest
    iljy
    aleks2,
    Вообще говоря запрос можно построить так:

    
    SELECT [Дата новой цены], [Цена], [Товар], 
         (SELECT TOP 1 [Цена] FROM Table t2 WHERE t2.[Дата новой цены] < t1.[Дата новой цены]
          ORDER BY [[Дата новой цены]]) as PrevPrice
    FROM Table t1
    

    планировщик обычно с такими справляется, если индексы есть.
    если нужна еще и предыдущая дата - будет сложнее, посколько 2000, но тоже можно.


    SELECT [Дата новой цены], [Цена], [Товар], 
         (SELECT TOP 1 [Цена] 
          FROM Table t2 
          WHERE t2.[Дата новой цены] < t1.[Дата новой цены] 
            AND t2.[Товар] = t1.[Товар] 
          ORDER BY [Дата новой цены]) as PrevPrice
    FROM Table t1
    
    29 май 09, 12:31    [7243677]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    iljy
    Guest
    aleks2
    iljy
    aleks2,
    Вообще говоря запрос можно построить так:

    
    SELECT [Дата новой цены], [Цена], [Товар], 
         (SELECT TOP 1 [Цена] FROM Table t2 WHERE t2.[Дата новой цены] < t1.[Дата новой цены]
          ORDER BY [[Дата новой цены]]) as PrevPrice
    FROM Table t1
    

    планировщик обычно с такими справляется, если индексы есть.
    если нужна еще и предыдущая дата - будет сложнее, посколько 2000, но тоже можно.

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


    а много - это сколько? у меня подобный запрос выдает по таблице из 30 млн значений вборку по 10000 "товаров" в течении 2 секунд. причем это тестовая база, на моей рабочей машине, а отнюдь не на сервере.

    гм-гм,
    ну простите, опечатался:)
    29 май 09, 13:21    [7244056]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    aleks2
    Guest
    iljy

    а много - это сколько? у меня подобный запрос выдает по таблице из 30 млн значений вборку по 10000 "товаров" в течении 2 секунд. причем это тестовая база, на моей рабочей машине, а отнюдь не на сервере.


    1. На сервере быстрее не будет.
    2. Много - это по-разному. Вопче то 2 сек. - это ОЧЕНЬ много. Ведь такие фокусы зачастую делают для ДАЛЬНЕЙШЕГО использования в ДРУГИХ запросах...
    29 май 09, 13:55    [7244363]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    iljy
    Guest
    aleks2

    1. На сервере быстрее не будет.
    2. Много - это по-разному. Вопче то 2 сек. - это ОЧЕНЬ много. Ведь такие фокусы зачастую делают для ДАЛЬНЕЙШЕГО использования в ДРУГИХ запросах...


    будет-будет. и чтение быстрее, и кэш больше. кстати для справки - 2 секунды - это время первого выполнения, когда перелопачивается таблица на диске. повторное выполнение, когда индексы и данные в памяти - мгновенно (elapsed time = 289 ms), причем все это время падает на извлечение значений из таблицы (99% - Index Seek)

    И запрос этот кстати именно что является куском другого запроса. Но вообще это лирика, а если хотите конкретики - задавайте конкретные вопросы. Например - вам нужно получить все данные и для каждой записи разницу или получить для каждого товара последнюю запись в интервале дат и разницу для нее? В первом случае - проще гнать с сервера отсортированный поток данных и разницу с предыдущей считать на клиенте, во втором - возможны варианты.
    29 май 09, 14:24    [7244597]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    aleks2
    Guest
    iljy
    aleks2

    1. На сервере быстрее не будет.
    2. Много - это по-разному. Вопче то 2 сек. - это ОЧЕНЬ много. Ведь такие фокусы зачастую делают для ДАЛЬНЕЙШЕГО использования в ДРУГИХ запросах...


    будет-будет. и чтение быстрее, и кэш больше. кстати для справки - 2 секунды - это время первого выполнения, когда перелопачивается таблица на диске. повторное выполнение, когда индексы и данные в памяти - мгновенно (elapsed time = 289 ms), причем все это время падает на извлечение значений из таблицы (99% - Index Seek)

    И запрос этот кстати именно что является куском другого запроса. Но вообще это лирика, а если хотите конкретики - задавайте конкретные вопросы. Например - вам нужно получить все данные и для каждой записи разницу или получить для каждого товара последнюю запись в интервале дат и разницу для нее? В первом случае - проще гнать с сервера отсортированный поток данных и разницу с предыдущей считать на клиенте, во втором - возможны варианты.


    Дарагой, если тебя все устраивает - чего тебе надо? Шоб я признал, что коррелированный подзапрос - это хорошо? Дык, плохо это.

    >>будет-будет
    Скока запросов ты написал в свой жизни? Вот-вот...
    29 май 09, 14:33    [7244673]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    NewcomerSQL
    Member

    Откуда:
    Сообщений: 8
    Именно! Все данные и для каждой записи разницу. И разница эта только внутри конкретной группы товара. А если для какого-то товара только одна запись?
    29 май 09, 14:45    [7244784]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    iljy
    Guest
    aleks2


    Дарагой, если тебя все устраивает - чего тебе надо? Шоб я признал, что коррелированный подзапрос - это хорошо? Дык, плохо это.

    Вообще этот корелированный подзапрос появился не просто так, а был выбран из многих вариантов и оказался оптимальным по производительности, перебивая join c группировкой. Хотя для 2005 он был в итоге переписан с использованием outer apply, но не потому что быстрее, а потому что стали нужны 2 последние записи.

    >>будет-будет
    Скока запросов ты написал в свой жизни? Вот-вот...


    этот запрос выбирался из многих вариантов еще на SQL 7.0. И еще вам для справки - для Postgres был выбран другой запрос, потому что там планировщик работает маленько по другому.
    Такшо нинада пантавацца;)
    29 май 09, 14:53    [7244856]     Ответить | Цитировать Сообщить модератору
     Re: Предыдущая запись в группе.  [new]
    iljy
    Guest
    NewcomerSQL
    Именно! Все данные и для каждой записи разницу. И разница эта только внутри конкретной группы товара. А если для какого-то товара только одна запись?


    ну и что?
    SELECT [Дата новой цены], [Цена], [Товар]
    FROM Table t1
    ORDER BY [Товар], [Дата новой цены]
    
    дальше в цикле выборку, и храните значение предыдущей цены до смены кода товара. А если одна запись - это уж что вам нужно по бизнеслогике, можете NULL ставить. Для сервера кстати эту задачу все равно придется решать.

    хотя конечно если вы загружаете в какой-нибудь грид - тогда да, нужно сформировать все на сервере. можно попробовать еще что-нибудь вроде такого
    select  t1.[Дата новой цены], t1.[Цена], t1.[Товар], t2.[Дата новой цены]
    from Table1 t1
    	left join
           Table1 t2
             on t2.[Дата новой цены] < t1.[Дата новой цены]
    group by t1.[Дата новой цены], t1.[Цена], t1.[Товар]
    
    29 май 09, 15:02    [7244922]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить