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

Откуда:
Сообщений: 23
 		Утра доброго, господа.
Помогите пожалуйста.
Есть скрипт, суммирующий все действия пользователя за день.
SELECT *
FROM (
SELECT        
--Разбиваем все действия по дням
[Дата] = DATEFROMPARTS(DATEPART(yy, [Время]), DATEPART(mm, [Время]), 	DATEPART(dd, [Время])), 
[Пользователь]
FROM            
[БД] dec) t 
PIVOT (
COUNT([Пользователь]) 
FOR [Пользователь] IN 
([Юзер 1], 
[Юзер 2], 
[Юзер 3])
) p

Требуется посмотреть, сколько максимум действий за 1 час каждый день совершал пользователь (выборка из всего дня, но разбитая по часам, с 00:00 до 23:59)
18 июл 18, 10:26    [21580156]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
Mazay2142
Member

Откуда:
Сообщений: 23
 		То есть, было так (сколько действий за весь день):
Дата Юзер 1 Юзер 2 Юзер 3
18.июл 90 45 180
17.июл 180 90 45
16.июл 45 180 90


Стало так (Максимум действий за 1 час):
Дата Юзер 1 Юзер 2 Юзер 3
18.июл 10 5 20
17.июл 20 10 5
16.июл 5 20 10
18 июл 18, 10:33    [21580187]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32174
Mazay2142
Требуется посмотреть, сколько максимум действий за 1 час каждый день совершал пользователь (выборка из всего дня, но разбитая по часам, с 00:00 до 23:59)
Эээ, такой же запрос, но разбивать по часам, а не по дням. В чём проблема то?
18 июл 18, 10:34    [21580190]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 291
 		Mazay2142,
SELECT sum([Юзер 1]) as count1, max([Юзер 1]) as Max1
sum([Юзер 2]) as count2, max([Юзер 2]) as Max2,  
sum([Юзер 3]) as count3, max([Юзер 3]) as Max3
From (Select * 
FROM (
SELECT        
--Разбиваем все действия по дням
[Дата] = DATEFROMPARTS(DATEPART(yy, [Время]), DATEPART(mm, [Время]), 	DATEPART(dd, [Время]), 	DATEPART(hh, [Время])), 
[Пользователь]
FROM            
[БД] dec) t 
PIVOT (
COUNT([Пользователь]) 
FOR [Пользователь] IN 
([Юзер 1], 
[Юзер 2], 
[Юзер 3])
) p) a
18 июл 18, 10:36    [21580197]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
Mazay2142
Member

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

вот, не могу понять, как разбить и по часам (не выводя часы), и по дням (выводя дни), а из часов ещё максимальное кол-во действий вытащить
18 июл 18, 10:38    [21580202]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 291
 		Если по датам:


SELECT DATEFROMPARTS(DATEPART(yy, [Дата]), DATEPART(mm, [Дата]), 	DATEPART(dd, [Дата])), 
sum([Юзер 1]) as count1, max([Юзер 1]) as Max1
sum([Юзер 2]) as count2, max([Юзер 2]) as Max2,  
sum([Юзер 3]) as count3, max([Юзер 3]) as Max3
From (Select * 
FROM (
SELECT        
--Разбиваем все действия по дням
[Дата] = DATEFROMPARTS(DATEPART(yy, [Время]), DATEPART(mm, [Время]), 	DATEPART(dd, [Время]), 	DATEPART(hh, [Время])), 
[Пользователь]
FROM            
[БД] dec) t 
PIVOT (
COUNT([Пользователь]) 
FOR [Пользователь] IN 
([Юзер 1], 
[Юзер 2], 
[Юзер 3])
) p) a
Group by DATEFROMPARTS(DATEPART(yy, [Дата]), DATEPART(mm, [Дата]), 	DATEPART(dd, [Дата]))
18 июл 18, 10:39    [21580204]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
Mazay2142
Member

Откуда:
Сообщений: 23
Kopelly
Если по датам:


SELECT DATEFROMPARTS(DATEPART(yy, [Дата]), DATEPART(mm, [Дата]), 	DATEPART(dd, [Дата])), 
sum([Юзер 1]) as count1, max([Юзер 1]) as Max1
sum([Юзер 2]) as count2, max([Юзер 2]) as Max2,  
sum([Юзер 3]) as count3, max([Юзер 3]) as Max3
From (Select * 
FROM (
SELECT        
--Разбиваем все действия по дням
[Дата] = DATEFROMPARTS(DATEPART(yy, [Время]), DATEPART(mm, [Время]), 	DATEPART(dd, [Время]), 	DATEPART(hh, [Время])), 
[Пользователь]
FROM            
[БД] dec) t 
PIVOT (
COUNT([Пользователь]) 
FOR [Пользователь] IN 
([Юзер 1], 
[Юзер 2], 
[Юзер 3])
) p) a
Group by DATEFROMPARTS(DATEPART(yy, [Дата]), DATEPART(mm, [Дата]), 	DATEPART(dd, [Дата]))


Пишет ошибку, что не знает такого столбца [Дата]
18 июл 18, 10:45    [21580221]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
Mazay2142
Member

Откуда:
Сообщений: 23
 		Но пишет только про 1ую строчку
18 июл 18, 10:46    [21580225]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
Mazay2142
Member

Откуда:
Сообщений: 23
 		Column 'a.Дата' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Kopelly
Если по датам:


SELECT DATEFROMPARTS(DATEPART(yy, [Дата]), DATEPART(mm, [Дата]), 	DATEPART(dd, [Дата])), 
sum([Юзер 1]) as count1, max([Юзер 1]) as Max1
sum([Юзер 2]) as count2, max([Юзер 2]) as Max2,  
sum([Юзер 3]) as count3, max([Юзер 3]) as Max3
From (Select * 
FROM (
SELECT        
--Разбиваем все действия по дням
[Дата] = DATEFROMPARTS(DATEPART(yy, [Время]), DATEPART(mm, [Время]), 	DATEPART(dd, [Время]), 	DATEPART(hh, [Время])), 
[Пользователь]
FROM            
[БД] dec) t 
PIVOT (
COUNT([Пользователь]) 
FOR [Пользователь] IN 
([Юзер 1], 
[Юзер 2], 
[Юзер 3])
) p) a
Group by DATEFROMPARTS(DATEPART(yy, [Дата]), DATEPART(mm, [Дата]), 	DATEPART(dd, [Дата]))
18 июл 18, 11:03    [21580274]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
 
declare @t table (dt datetime, u varchar(10));

insert into @t
 (dt, u)
 select top(10000)
  dateadd(mi, row_number() over (order by (select 1)), '2018'), 'u' + cast(cast(rand(checksum(newid())) * 3 + 1 as int) as varchar(10))
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

select
 p.*
from
 (
  select cast(dt as date), u, count(*) over (partition by u, datediff(hour, '1900', dt)) from @t
 ) t(dt, u, cnt)
pivot
(
 max(cnt)
 for u in ([u1], [u2], [u3]) 
) p;
18 июл 18, 11:33    [21580402]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 291
 
SELECT DATEFROMPARTS(DATEPART(yy, [Дата]), DATEPART(mm, [Дата]), 	DATEPART(dd, [Дата])), 
sum([Юзер 1]) as count1, max([Юзер 1]) as Max1,
sum([Юзер 2]) as count2, max([Юзер 2]) as Max2,  
sum([Юзер 3]) as count3, max([Юзер 3]) as Max3
From (Select * 
FROM (
SELECT        
--Разбиваем все действия по дням
[Дата] = DATETIMEFROMPARTS(DATEPART(yy, [Время]), DATEPART(mm, [Время]), 	DATEPART(dd, [Время]), 	DATEPART(hh, [Время]),0,0,0), 
[Пользователь]
FROM            
[БД] dec) t 
PIVOT (
COUNT([Пользователь]) 
FOR [Пользователь] IN 
([Юзер 1], 
[Юзер 2], 
[Юзер 3])
) p) a
Group by DATEFROMPARTS(DATEPART(yy, [Дата]), DATEPART(mm, [Дата]), 	DATEPART(dd, [Дата]))
18 июл 18, 11:38    [21580422]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
Mazay2142
Member

Откуда:
Сообщений: 23
 		Работает, но работатет также, как изначальный скрипт... считает не по часам, а действий всего (хотя оставил только максимумы)
Считает максимум за день всего, а не за час...
6 столбец - я, за час я никак 138 действий не сделал в системе
Kopelly
SELECT DATEFROMPARTS(DATEPART(yy, [Дата]), DATEPART(mm, [Дата]), 	DATEPART(dd, [Дата])), 
sum([Юзер 1]) as count1, max([Юзер 1]) as Max1,
sum([Юзер 2]) as count2, max([Юзер 2]) as Max2,  
sum([Юзер 3]) as count3, max([Юзер 3]) as Max3
From (Select * 
FROM (
SELECT        
--Разбиваем все действия по дням
[Дата] = DATETIMEFROMPARTS(DATEPART(yy, [Время]), DATEPART(mm, [Время]), 	DATEPART(dd, [Время]), 	DATEPART(hh, [Время]),0,0,0), 
[Пользователь]
FROM            
[БД] dec) t 
PIVOT (
COUNT([Пользователь]) 
FOR [Пользователь] IN 
([Юзер 1], 
[Юзер 2], 
[Юзер 3])
) p) a
Group by DATEFROMPARTS(DATEPART(yy, [Дата]), DATEPART(mm, [Дата]), 	DATEPART(dd, [Дата]))


К сообщению приложен файл. Размер - 85Kb
18 июл 18, 11:51    [21580465]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
Mazay2142
Member

Откуда:
Сообщений: 23
 		ничего непонятно, как это к задаче относится...
объясните пожалуйста и сопоставьте с поставленной задачей
invm
declare @t table (dt datetime, u varchar(10));

insert into @t
 (dt, u)
 select top(10000)
  dateadd(mi, row_number() over (order by (select 1)), '2018'), 'u' + cast(cast(rand(checksum(newid())) * 3 + 1 as int) as varchar(10))
 from
  master.dbo.spt_values a cross join
  master.dbo.spt_values b;

select
 p.*
from
 (
  select cast(dt as date), u, count(*) over (partition by u, datediff(hour, '1900', dt)) from @t
 ) t(dt, u, cnt)
pivot
(
 max(cnt)
 for u in ([u1], [u2], [u3]) 
) p;
18 июл 18, 11:56    [21580482]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 291
 		Mazay2142, В запросе:
sum([Юзер Х]) - общее кол-во за день (как в исходном запросе)
max([Юзер Х]) - максимальное за час в указанный день (то что хотел получить)
18 июл 18, 11:58    [21580489]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
Kopelly
Member

Откуда: Красноярск
Сообщений: 291
Mazay2142
ничего непонятно, как это к задаче относится...
объясните пожалуйста и сопоставьте с поставленной задачей

Первая часть (declare .... и Insert into ....) - генерация данных для проверки
Вторая часть сам запрос, только для удобства написания ввели короткие наименования полей и таблицы:
@t ==> [БД]
dt ==> [Дата]
u ==> [Пользователь]
18 июл 18, 12:03    [21580510]     Ответить | Цитировать Сообщить модератору
 Re: Пики вошедших в систему, разбитые по часам.  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
Mazay2142
объясните пожалуйста и сопоставьте с поставленной задачей
Т.е. 21580187 не вы писали?
18 июл 18, 12:30    [21580651]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить