Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Кривой запрос  [new]
Если
Member

Откуда:
Сообщений: 58
Добрый день.

Есть задача:
- Вычислить топ3 загрузок файла по месяцам.
- Посчитать изменения в % к предыдущему месяцу для позиций из топ3. (если файл попал в топ3 загрузок первый раз, то изменение = пусто).

Вот такой запрос, но похоже можно сделать гораздо элегантнее, помогите пж-та.

DECLARE @file TABLE (file_id  int
                        ,title varchar(130)  NULL)

DECLARE @download TABLE (file_id int
							  , date DATETIME
							  , user_id int )

INSERT @file
VALUES (1,N'file1'), (2,N'file2')
     
INSERT @download
VALUES (1,'2018-10-01T21:20:27.033',100), (2,'2018-10-02T21:22:25.033',101), (1,'2018-10-02T21:22:28.033',102), (1,'2018-10-02T22:22:27.033',103)
, (1,'2018-09-01T21:20:27.033',100), (2,'2018-09-02T21:22:25.033',101)


--запрос
select t1.dat [Текущий месяц]
		, num [порядковый номер в top3]
		, t1.title [имя файла]
		, t1.u [количество юзеров]
		, CONVERT(varchar(10),(t1.u/t2.u)*100)+'%'[разница с прошлым месяцем]
from 
( -- текущий месяц
select distinct top 10 substring(CONVERT(varchar(10), cw.date, 23),1,7) dat
		, ROW_NUMBER() over(ORDER BY count(distinct user_id) desc) num
		, title
		, count(distinct user_id) u
		, c.file_id
from @file c
left join 
@download cw
on c.file_id = cw.file_id
where  year(cw.date)=year(GETDATE()) and month(cw.date)=month(GETDATE())
group by substring(CONVERT(varchar(10), cw.date, 23),1,7), title, c.file_id
)t1
left join
( -- прошлый месяц
select distinct top 10 substring(CONVERT(varchar(10), cw.date, 23),1,8) dat
		, title
		, count(distinct user_id) u
		, c.file_id
from @file c
left join 
@download cw
on c.file_id = cw.file_id
where  year(cw.date)=year(GETDATE()) and month(cw.date)=month(dateadd(month, -1, getdate()))
group by substring(CONVERT(varchar(10), cw.date, 23),1,8), title, c.file_id
)t2
on t1.file_id=t2.file_id
18 окт 18, 23:00    [21708304]     Ответить | Цитировать Сообщить модератору
 Re: Кривой запрос  [new]
Владимир Затуливетер
Member

Откуда:
Сообщений: 427
declare @file table ( file_id int, title varchar(130) null );
declare @download table ( file_id int, date datetime, user_id int );

insert @file
values ( 1, N'file1' )
     , ( 2, N'file2' );

insert @download
values ( 1, '2018-10-01T21:20:27.033', 100 )
     , ( 2, '2018-10-02T21:22:25.033', 101 )
     , ( 1, '2018-10-02T21:22:28.033', 102 )
     , ( 1, '2018-10-02T22:22:27.033', 103 )
     , ( 1, '2018-09-01T21:20:27.033', 100 )
     , ( 2, '2018-09-02T21:22:25.033', 101 );

declare @Now date = getdate();
declare @CurMonth date = datefromparts(year(@Now), month(@Now), 1);
declare @PrevMonth date = dateadd(month, -1, @CurMonth);

with cteCurMonth
as (
    select top 10 
        file_id
      , count(distinct user_id) as cnt
      , row_number() over ( order by count(distinct user_id) desc ) as num
    from @download
    where date >= @CurMonth
    group by file_id
    order by cnt desc
),
ctePrevMonth
as (
    select top 10 file_id, count(distinct user_id) as cnt
    from @download
    where date >= @PrevMonth and date < @CurMonth
    group by file_id
    order by cnt desc
)
select f.file_id
     , f.title
     , c.num
     , c.cnt
     , c.cnt / p.cnt * 100 as DiffPercent
from @file f 
    join cteCurMonth c on c.file_id = f.file_id
    join ctePrevMonth p on p.file_id = f.file_id


  • используйте cte для выделения составных частей запроса
  • не надо джойнить @file раньше времени, сначала посчитайте агрегаты
  • форматируйте числа и даты в клиентском приложении, а не в запросе: substring(CONVERT(varchar(10), cw.date, 23),1,7) dat
  • фильтруйте данные чтобы использовались индексы, year(cw.date)=? and month(cw.date)=? вот с таким подходом индексы не используются.
  • top без order by - ошибка, ответьте на вопрос какие top записи вам нужны
  • форматируйте текст запроса, запрос это тоже программа, не надо из него делать кашу.
  • в место комментов "текущий месяц" можно просто использовать алиасы cteCurMonth к примеру, т.е. пишите самодокументируемый код.
  • не повторяйте по нескольку раз одно и тоже: к примеру substring(convert(varchar(10), cw.date, 23), 1, 8)

    И думаю более производительный вариант будет выглядеть так, но зависит от индексов на таблице @download

    with cteCurMonth
    as (
        select top 3 
            file_id
          , count(distinct user_id) as cnt
          , row_number() over ( order by count(distinct user_id) desc ) as num
        from @download
        where date >= @CurMonth
        group by file_id
        order by cnt desc
    )
    select top 3
           f.file_id
         , f.title
         , cur.num
         , cur.cnt
         , cur.cnt / prev.cnt * 100 as DiffPercent
    from @file f 
        join cteCurMonth cur on cur.file_id = f.file_id
        outer apply (
            select d.file_id, count(distinct d.user_id) as cnt
            from @download d
            where d.date >= @PrevMonth and d.date < @CurMonth
              and d.file_id = f.file_id
            group by d.file_id
        ) prev
    order by cur.cnt desc
    go
    
  • 19 окт 18, 06:00    [21708380]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить