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

Откуда:
Сообщений: 24290
Wlr-l
Glory,
Здесь уже все знают, что Glory и АлексВторой самые крутые, а мы так... пописать вышли. Поэтому можно без демонстраций обойтись!
ето вы еще с Мниором не спорили о выших материях.. или с Кримеаном план запросов не разберали
11 сен 14, 10:01    [16562583]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Wlr-l
Member

Откуда:
Сообщений: 523
И все же, как сформировать данные для гистограммы, т.е. сделать то, о чем просил ТС.
11 сен 14, 14:03    [16563841]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Стас0н
Member

Откуда:
Сообщений: 106
Сейчас научился выгружать данные:

+-----------+---------------------+-------------------+
|  site_id  | visitor_id          | count(visitor_id) |
+-----------+---------------------+-------------------+
|         8 |  353389751327572831 |              1120 |
|         8 | 8248747651386193561 |              1005 |
|         8 | 1773132781390152736 |               957 |
|         8 |  501663501391419490 |               936 |
|         8 | 1231874741355575425 |               925 |
|         8 | 2078329811388561752 |               904 |
|         8 | 7565480111354343102 |               875 |
|         8 |  997915071394432214 |               870 |
|         8 | 2363204321364319637 |               869 |
|         8 |   17474831359576968 |               866 |
+-----------+---------------------+-------------------+


Как теперь бы построить гистограмму "в длину" как я писал в начале темы, а не как по ссылке
11 сен 14, 16:11    [16564732]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Maxx_UA
Guest
Стас0н,

вы совершенно не хотите слышать что mssql не умеет строить гистограмы и всякое такое - ето задача КЛИЕНТСКОГО приложения
11 сен 14, 16:27    [16564844]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Wlr-l
Member

Откуда:
Сообщений: 523
[url=]https://ru.wikipedia.org/wiki/Гистограмма_(статистика)[/url]


[url=]http://office.microsoft.com/ru-ru/excel-help/HA010238252.aspx[/url]
11 сен 14, 16:58    [16565076]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Стас0н
Member

Откуда:
Сообщений: 106
Maxx_UA
Стас0н,
вы совершенно не хотите слышать что mssql не умеет строить гистограмы и всякое такое - ето задача КЛИЕНТСКОГО приложения


Виноват, правильнее было бы сказать не гистограмма, а просто само распределение. Опять же - как в начале темы, т.е.:

   site_ID        1    2    3    4    5
    ----------    ---  ---  ---  ---  ---
    1             967  576  856  234  22
    2             576  947  847  987  324
    .
    .
11 сен 14, 17:48    [16565407]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
o-o
Guest
самый первый же ответ на первой странице:
_human
Стас0н,
PIVOT избавит от всех проблем.

 declare @t table (site_id int, visitor_id bigint);
  
 insert into @t (site_id, visitor_id)
 values
(8, 14441319829303) ,
(8, 14441319829303) ,
(8, 14441319829303) ,
(8, 16664540522788) ,
(8, 31051333527785) ,
(8, 31051333527785) ,
(8, 43544524742951) ,
(9, 43544524742951) ,
(9, 59231398610260) ,
(9, 62321349676603); 

select *
from (
		select site_id, count(visitor_id) as cnt
		from @t
		group by site_id) s
pivot (avg(cnt) for cnt in ([1], [2], [3], [4], [5], [6], [7]))p;		
11 сен 14, 18:09    [16565489]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Wlr-l
Member

Откуда:
Сообщений: 523
Стас0н,

1. И в чем же Вы виноваты? Я привел ссылку на определение гистограммы. Следовательно, гистограмма всего лишь кусочно-линейная функция, которая задается таблицей (сторона как сервера, так и клиента). Чаще всего эту таблицу приводят к графическому виду - столбиковой диаграмме (сторона клиента). Поэтому, все кто говорит, что на T-SQL нельзя построить гистограмму просто некомпетентны в этом вопросе.

2.Вы используете следующие интервалы: 1-1, 2-2, 3-3, 4-4, 5-5. И был задан вопрос, что делать, если больше 5?

3.Вам были даны три решения, Вас они не устроили. То Вам как в статье, то не как в статье. Определитесь.

4.Вы не привели "хороших" данных для демонстрации решений. В последней таблице один сайт, а кол-во посещений больше 5. Т.е. получается одна строка:

8, 0, 0, 0, 0, 0
11 сен 14, 18:15    [16565512]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Стас0н
Member

Откуда:
Сообщений: 106
Я просто хочу, чтобы можно было во временно таблицу один раз прописать все интервалы и дальше ничего руками не делать.

Pivot, на сколько я понял, требует вбивания руками названия столбцов
В решении https://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=1114165&msg=16549193 слишком много руками править, если вдруг изменятся интервалы

В решении, ссылку на котороя я привел результат выводится в столбец, а не в строку
11 сен 14, 18:38    [16565585]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Стас0н
Member

Откуда:
Сообщений: 106
Реальные данные, например, такие:

+-----------+---------------------+-------------------+
| adnest_id | visitor_id          | count(visitor_id) |
+-----------+---------------------+-------------------+
|         8 |  353389751327572831 |              1120 |
|         8 | 8248747651386193561 |              1005 |
|         8 | 1773132781390152736 |               957 |
|         8 |  501663501391419490 |               936 |
|         5 | 1231874741355575425 |               925 |
|         5 | 2078329811388561752 |               904 |
|         2 | 7565480111354343102 |               875 |
|         2 |  997915071394432214 |               870 |
|         2 | 2363204321364319637 |               869 |
|         2 |   17474831359576968 |               866 |
+-----------+---------------------+-------------------+

интвервалы:

create temporary table ranges (
   s int not null,
   e int not null
);

insert into ranges (s, e) values
   (0, 10),
   (11, 20),
   (21, 30),
   (31, 40),
   (41, 50),
   (51, 100),
   (101, 300),
   (301, 500),
   (501, 1000),
   (1001, 1500),
   (1500, 10000)
;
11 сен 14, 18:42    [16565598]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
o-o
Guest
Стас0н
create temporary table ranges (
   s int not null,
   e int not null
);


ораклоид что-ли?

declare @t table (site_id int, visitor_id bigint, cnt_v int);
  
 insert into @t (site_id, visitor_id, cnt_v)
 values
(8,  353389751327572831,1120 ),
(8, 8248747651386193561,1005 ),
(8, 1773132781390152736, 957 ),
(8,  501663501391419490, 936 ),
(5, 1231874741355575425, 925 ),
(5, 2078329811388561752, 904 ),
(2, 7565480111354343102, 875 ),
(2,  997915071394432214, 870 ),
(2, 2363204321364319637, 869 ),
(2,   17474831359576968, 866 );

declare @ranges table(
   id int identity, 
   s int not null,
   e int not null
);

insert into @ranges (s, e) values
   (0, 10),
   (11, 20),
   (21, 30),
   (31, 40),
   (41, 50),
   (51, 100),
   (101, 300),
   (301, 500),
   (501, 1000),
   (1001, 1500),
   (1500, 10000)
;
with c1 as
(
select site_id, sum(cnt_v) as cnt
from @t
group by site_id
),

c2 as
(
select c1.site_id, c1.cnt, r.id
from @ranges r join c1 on c1.cnt between r.s and r.e
)
 
select *
from c2
pivot (avg(cnt) for id in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11]))p;	
-------------
site_id	1	2	3	4	5	6	7	8	9	10	11
2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	3480
5	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1829
8	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	4018


вместо 1..11 интервалы сами подставьте
12 сен 14, 01:57    [16566527]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Стас0н
Member

Откуда:
Сообщений: 106
Благодарю за ответ!
Но интересует как можно обойтись без:
pivot (avg(cnt) for id in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11]))p;	


Хотелось бы чтобы кол-во столбцов и их название определялось не ручным вбиванием, а используя таблицу ranges
12 сен 14, 02:01    [16566529]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
o-o
Guest
if object_id('tempdb..#t') is not null drop table #t;
create table #t (site_id int, visitor_id bigint, cnt_v int);
  
 insert into #t (site_id, visitor_id, cnt_v)
 values
(8,  353389751327572831,1120 ),
(8, 8248747651386193561,1005 ),
(8, 1773132781390152736, 957 ),
(8,  501663501391419490, 936 ),
(5, 1231874741355575425, 925 ),
(5, 2078329811388561752, 904 ),
(2, 7565480111354343102, 875 ),
(2,  997915071394432214, 870 ),
(2, 2363204321364319637, 869 ),
(2,   17474831359576968, 866 );

if object_id('tempdb..#ranges') is not null drop table #ranges;
create table #ranges(
   id int identity, 
   s int not null,
   e int not null,
   r_name as '(' + cast(s as varchar(10)) + ', ' + cast(e as varchar(10)) + ')'
);

insert into #ranges (s, e) values
   (0, 10),
   (11, 20),
   (21, 30),
   (31, 40),
   (41, 50),
   (51, 100),
   (101, 300),
   (301, 500),
   (501, 1000),
   (1001, 1500),
   (1500, 10000)
;

declare @sql varchar(max);

set @sql =
'with c1 as ' +
'( ' +
'select site_id, sum(cnt_v) as cnt ' +
'from #t ' +
'group by site_id ' +
'), ' +

'c2 as ' +
'( ' +
'select c1.site_id, c1.cnt, r.r_name ' +
'from #ranges r join c1 on c1.cnt between r.s and r.e ' +
') ' +
 
'select * ' +
'from c2 ' +
'pivot (avg(cnt) for r_name in (';

set @sql = @sql +
(select stuff((select ',' + '[' + r_name + ']'
             from #ranges 
             order by id 
             for xml path('')),1,1,'')) +
'))p; ' ;	


--print @sql;

exec (@sql);
---
site_id	(0, 10)	(11, 20)	(21, 30)	(31, 40)	(41, 50)	(51, 100)	(101, 300)	(301, 500)	(501, 1000)	(1001, 1500)	(1500, 10000)
2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	3480
5	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	1829
8	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	4018


К сообщению приложен файл. Размер - 1Kb
12 сен 14, 14:21    [16568507]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Стас0н
Member

Откуда:
Сообщений: 106
Спасибо большое!
Возник новый вопрос - а как получить распределение количества пользователей по частоте для каждого сайта?
Т.е. теперь, если рассматривать пример выше, для таблицы со значенями:
(8,  353389751327572831,1120 ),
(8, 8248747651386193561,1005 ),
(8, 1773132781390152736, 957 ),
(8,  501663501391419490, 936 ),
(5, 1231874741355575425, 925 ),
(5, 2078329811388561752, 904 ),
(2, 7565480111354343102, 875 ),
(2,  997915071394432214, 870 ),
(2, 2363204321364319637, 869 ),
(2,   17474831359576968, 866 );


распределение будет выглядеть так:
site_id	(0, 10)	(11, 20)	(21, 30)	(31, 40)	(41, 50)	(51, 100)	(101, 300)	(301, 500)	(501, 1000)	(1001, 1500)	(1500, 10000)
2	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	4	NULL	NULL
5	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	2	NULL	NULL
8	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	2	NULL
15 сен 14, 03:32    [16574694]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Стас0н
Member

Откуда:
Сообщений: 106
я вот пока вот такой запрос написал (без вывода результат в строчку):
create temporary table ranges 
(
   s int not null,
   e int not null,
   range_name varchar(15) not null
);

insert into ranges (s, e) values
   (0, 100),
   (101, 200),
   (201, 300),
   (301, 1000),
   (1001, 1500),
   (1501, 10000)
;

update ranges set range_name = concat(s, '-', e);

withtab as
(
select site_id, visitor_id, count(visitor_id) as frequency
from hist_impressions
inner join timeslot t on t.timeslot_id = hist_impressions.timeslot_id
where start_date between '2014-05-01 00:00:00' and '2014-09-08 00:00:00'
group by visitor_id
order by site_id, count(visitor_id) desc
limit 50;
)

select 
	site_id, 
	range_name, 
	cnt=COUNT(case when tab.frequency >= ranges.s and tab.frequency <= ranges.e then 1 else null)
from ranges join on tab.frequency between ranges.s and ranges.e
group by site_id


Однако до select не доходит, выводит ошибку при формировании общего табличного выражения tab - you have an error in SQL syntax.. Косяка не вижу пока
15 сен 14, 04:05    [16574705]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Glory
Member

Откуда:
Сообщений: 104751
Стас0н
я вот пока вот такой запрос написал

И где вы нашли синтаксис вроде create temporary table, limit 50 ?
15 сен 14, 09:22    [16574866]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Стас0н
Member

Откуда:
Сообщений: 106
MySQl 5.5...
15 сен 14, 09:51    [16574949]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Glory
Member

Откуда:
Сообщений: 104751
Стас0н
MySQl 5.5...

А MS SQL тут причем ?
15 сен 14, 09:53    [16574956]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Стас0н
Member

Откуда:
Сообщений: 106
???
15 сен 14, 12:07    [16575621]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Glory
Member

Откуда:
Сообщений: 104751
Стас0н
???

Спрашивать про синитаксис MySQL нужно в форуме по MySQL
15 сен 14, 12:13    [16575666]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4805
Стас0н
???


MsSQL и MySQL это две разные СУБД
15 сен 14, 12:14    [16575667]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Стас0н
Member

Откуда:
Сообщений: 106
да, понял... на сколько я понимаю, проблема с общим табличными выражением. Собственно, на сколько понял, таковых вообще нет в MySQL
пожалуй заменю табличное выражение на временную таблицу.
15 сен 14, 12:32    [16575783]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Glory
Member

Откуда:
Сообщений: 104751
Стас0н
пожалуй заменю табличное выражение на временную таблицу.

В вашем примере СTE вообще не нужно. Нигде.
15 сен 14, 12:35    [16575800]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Стас0н
Member

Откуда:
Сообщений: 106
ну а как же тогда нужно организовать запрос?
15 сен 14, 12:36    [16575804]     Ответить | Цитировать Сообщить модератору
 Re: Получить распределение  [new]
Glory
Member

Откуда:
Сообщений: 104751
Стас0н
ну а как же тогда нужно организовать запрос?

Спрашивать про синитаксис MySQL нужно в форуме по MySQL
15 сен 14, 12:36    [16575810]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 2 3 [4] 5   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить