Множественные запросы на выборку с использованием цикла while в одной таблице? Является ли это возможным?

У меня 2 стола. В таблице А есть дата, ISBN (для книги), спрос (спрос на эту дату). Таблица B содержит дату, ISBN (для книги) и SalesRank.

Пример данных выглядит следующим образом: DailyBookFile содержит 150 тыс. записей для каждой даты, начиная с 2010 года (т. е. 150 тыс. * 365 дней * 8 лет). То же самое и с таблицей SalesRank, содержащей около 500 тысяч записей на каждую дату.

DailyBookFile       
Date        Isbn13         CurrentModifiedDemandTotal
20180122    9780955153075   13
20180122    9780805863567   9
20180122    9781138779396   1
20180122    9780029001516   9
20180122    9780470614150   42

SalesRank       
importdate  ISBN13          SalesRank
20180122    9780029001516   69499
20180122    9780470614150   52879
20180122    9780805863567   832429
20180122    9780955153075   44528
20180122    9781138779396   926435

Required Output     
Date        Avg_Rank    Book_Group
20180122    385154  Elite
20180121    351545  Elite
20180120    201545  Elite

Я хочу получить Top 200 CurrentModifiedDemand за каждый день и взять средний рейтинг.

Я не могу найти решение, так как я новичок в SQL.

Я начал с того, что получил Top 200 CurrentModifiedDemand за вчерашний день и получил средний рейтинг за прошлый год.

SELECT DBF.Filedate AS [Date],
       AVG(AMA.SalesRank) AS Avg_Rank,
       'Elite' AS Book_Group 
FROM [ODS].[wholesale].[DailyBookFile] AS DBF
INNER JOIN [ODS].[MarketplaceMonitor].[SalesRank] AS AMA ON (DBF.Isbn13 = AMA.ISBN13
                                                        AND DBF.FileDate = AMA.importdate)
WHERE DBF.Isbn13 IN (SELECT TOP 200 Isbn13
                     FROM [ODS].[wholesale].[DailyBookFile]
                     WHERE FileDate = 20180122
                       AND CAST(CurrentModifiedDemandTotal AS int) > 200)
  AND DBF.Filedate > 20170101
GROUP BY DBF.Filedate;

Но результат не тот, что я хочу. Итак, теперь мне нужен ISBN для 200 лучших CurrentModifiedDemand на каждый день и их средний рейтинг. Я пробовал с этим.

DECLARE @i int;
SET @i = 20180122;
WHILE (SELECT DISTINCT(DBF.Filedate)
       FROM [ODS].[wholesale].[DailyBookFile] AS DBF
       WHERE DBF.Filedate = @i) IS NOT NULL
BEGIN

    SELECT DBF.Filedate AS [Date],
           AVG(AMA.SalesRank) AS Avg_Rank,
           'Elite' AS Book_Group 
    FROM [ODS].[wholesale].[DailyBookFile] AS DBF
    INNER JOIN [ODS].[MarketplaceMonitor].[SalesRank] as AMA ON DBF.Isbn13 = AMA.ISBN13
                                                            AND DBF.FileDate = AMA.importdate
    WHERE DBF.Isbn13 in (SELECT TOP 200 Isbn13
                         FROM [ODS].[wholesale].[DailyBookFile]
                         WHERE FileDate = @i
                           AND CAST (CurrentModifiedDemandTotal AS int) > 500)
      AND DBF.Filedate = @i
    GROUP BY DBF.Filedate;

    SET @i = @i+1;

END

В этом я получаю один результат запроса выбора в каждом окне. Есть ли способ получить результат в одной таблице?

P.S. Список 200 лучших книг каждый день будет меняться в соответствии с CurrentModifiedDemand. Я хочу взять их среднее значение. рейтинг продаж за этот день.


sql-server while-loop with-statement
person Neil S    schedule 23.01.2018    source источник
comment
Вы отметили здесь mysql и sql-server? Что вы используете? Кроме того, использование цикла WHILE — плохая идея. Не могли бы вы опубликовать некоторые данные DDL, расходные образцы данных и ожидаемый результат? Вероятно, для этого будет гораздо лучший подход к набору данных.   -  person Larnu    schedule 23.01.2018
comment
это вопрос для mysql или для sql-сервера? они оба разные   -  person GuidoG    schedule 23.01.2018
comment
Независимо от СУБД, не бойтесь использовать пробелы и форматировать запросы. Читать это гораздо труднее, чем нужно.   -  person Sean Lange    schedule 23.01.2018
comment
Извините, я использую sql-сервер. Я попытаюсь переформулировать это.   -  person Neil S    schedule 23.01.2018
comment
В вашем ожидаемом выводе у вас есть значения для дат '20180121' и '20180120', однако для него нет образцов данных. Откуда берутся эти ценности?   -  person Larnu    schedule 23.01.2018
comment
@Larnu каждая дата имеет значения для ISBN, я просто показываю образцы данных для 1 даты и ожидаемый результат для нескольких дат. Извините, если это было для вас запутанным.   -  person Neil S    schedule 23.01.2018
comment
Разве функция (или хранимая процедура, как бы она ни называлась в SQL Server) не имела бы больше смысла, когда вы можете передать заданный параметр даты, и она вернет результаты на основе этой даты? У вас может быть даже необязательный параметр count, например, TopBooksByDate(‹date›, ‹count›) со значением по умолчанию 200.   -  person Anthony    schedule 23.01.2018

Ответы (2)


arrow_upward
2
arrow_downward

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

IF OBJECT_ID('tempdb..#books') IS NOT NULL
BEGIN
    DROP TABLE #books
END

CREATE TABLE #books (
    [Date] INT,
    [Avg_Rank] FLOAT,
    [Book_Group] VARCHAR(512)
);

DECLARE @i int;
SET @i = 20180122;

BEGIN TRY
WHILE (SELECT DISTINCT(DBF.Filedate)
    FROM [ODS].[wholesale].[DailyBookFile] AS DBF
    WHERE DBF.Filedate = @i) IS NOT NULL
BEGIN

    INSERT INTO #books (
        [Date],
        [Avg_Rank],
        [Book_Group]
    )
    SELECT DBF.Filedate AS [Date],
        AVG(AMA.SalesRank) AS Avg_Rank,
        'Elite' AS Book_Group 
    FROM [ODS].[wholesale].[DailyBookFile] AS DBF
    INNER JOIN [ODS].[MarketplaceMonitor].[SalesRank] as AMA ON DBF.Isbn13 = AMA.ISBN13
                                                            AND DBF.FileDate = AMA.importdate
    WHERE DBF.Isbn13 in (SELECT TOP 200 Isbn13
                        FROM [ODS].[wholesale].[DailyBookFile]
                        WHERE FileDate = @i
                        AND CAST (CurrentModifiedDemandTotal AS int) > 500)
    AND DBF.Filedate = @i
    GROUP BY DBF.Filedate;

    SET @i = @i+1;

END
END TRY
BEGIN CATCH
    IF OBJECT_ID('tempdb..#books') IS NOT NULL
    BEGIN
        DROP TABLE #books
    END
END CATCH

SELECT *
FROM #books

DROP TABLE #books

Использование переменной табличного типа дало бы более простой код, но при хранении больших объемов данных переменные табличного типа начинают терять производительность по сравнению с временными таблицами. Я не уверен, сколько строк является отсечкой, но по своему опыту я видел значительный прирост производительности при изменении табличного типа var на temp table при количестве строк более 10000. Для небольшого количества рядов может применяться обратное.

person Justinas Marozas    schedule 23.01.2018
comment
Вы упомянули об использовании табличной переменной cost, что хорошо, но вы по-прежнему используете цикл WHILE. Это также будет иметь высокую стоимость для себя. :) - person Larnu; 23.01.2018
comment
Хороший вопрос, @Larnu. Я был слишком сосредоточен на вопросе, как объединить результаты в цикле while :) - person Justinas Marozas; 23.01.2018

arrow_upward
1
arrow_downward

Это позволяет избежать затратного цикла WHILE и, как мне кажется, позволяет достичь поставленной цели:

CREATE TABLE #DailyBookFile ([Date] date,
                            Isbn13 bigint,
                            CurrentModifiedDemandTotal tinyint);

INSERT INTO #DailyBookFile
VALUES ('20180122',9780955153075,13),
       ('20180122',9780805863567,9 ),
       ('20180122',9781138779396,1 ),
       ('20180122',9780029001516,9 ),
       ('20180122',9780470614150,42);

CREATE TABLE #SalesRank (importdate date,
                        ISBN13 bigint,
                        #SalesRank int);
INSERT INTO #SalesRank
VALUES ('20180122',9780029001516,69499 ),
       ('20180122',9780470614150,52879 ),
       ('20180122',9780805863567,832429),
       ('20180122',9780955153075,44528 ),
       ('20180122',9781138779396,926435);
GO
WITH Ranks AS(
    SELECT SR.*,
           RANK() OVER (PARTITION By SR.importdate ORDER BY SR.#SalesRank) AS Ranking
    FROM #SalesRank SR
         JOIN #DailyBookFile DBF ON SR.ISBN13 = DBF.Isbn13
                               AND SR.importdate = DBF.[Date])
SELECT importdate AS [Date],
       AVG(#SalesRank) AS Avg_rank,
       'Elite' AS Book_Group
FROM Ranks
WHERE Ranking <= 200
GROUP BY importdate;

GO
DROP TABLE #DailyBookFile;
DROP TABLE #SalesRank;
person Larnu    schedule 23.01.2018