ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Кривой запрос
2 сообщений из 2, страница 1 из 1
Кривой запрос
    #39719618
Если
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Если
Гость
Добрый день.

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

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
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
...
Рейтинг: 0 / 0
18.10.2018, 23:00
| Ответить | Цитировать | Написать  
Кривой запрос
    #39719641
Владимир Затуливетер
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Владимир Затуливетер
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
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
...
Рейтинг: 0 / 0
19.10.2018, 06:00
| Ответить | Цитировать | Написать  
2 сообщений из 2, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Кривой запрос
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]