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

Есть такая таблица im:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+----------------+
| site_id | visitor_id     |
+-----------+----------------+
|         8 | 14441319829303 |
|         8 | 14441319829303 |
|         8 | 14441319829303 |
|         8 | 16664540522788 |
|         8 | 31051333527785 |
|         8 | 31051333527785 |
|         8 | 43544524742951 |
|         9 | 43544524742951 |
|         9 | 59231398610260 |
|         9 | 62321349676603 |
+-----------+----------------+



Таблица содержит сведения о посещении определенных пользователей определенных сайтов. Мне нужно получить распределение количества пользователей по частоте посещения за определеный промежуток времени:
Код: sql
1.
2.
3.
4.
5.
6.
  site_ID       1-10    11-20  21-30
    ----------  ---   ---  ---
    1           967   576   856
    2           576   947   847
    .
    .



Очень не хочется постоянно перебивать значения интервалось - т.е. хочется править их в одном месте и чтобы потом автоматически менялось в остальных(это если делать через PIVOT, например)

написал вот такой запрос:
Код: 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.
group by visitor_id
order by adnest_id, count(visitor_id) desc
limit 50
);

select 
	adnest_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 adnest_id

-------------------------
create temporary table tab
(
select 
	adnest_id, 
	visitor_id , 
	count(visitor_id)
from hist_impressions
inner join timeslot t on t.timeslot_id = hist_impressions.timeslot_id
where start_date between '2014-08-01 00:00:00' and '2014-09-08 00:00:00'
group by visitor_id
order by adnest_id, count(visitor_id) desc
limit 50
);



Получаю табличку:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+---------------------+-------------------+
|  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 |
+-----------+---------------------+-------------------+



Как дальше из нее можно достать нужное распределение?
...
Рейтинг: 0 / 0
15.09.2014, 12:51:45
| Ответить | Цитировать | Написать  
Достать распределение
    #38747061
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
MySQL не поддерживает PIVOT. Посему только статическое формирование. Возможно, программно на основе таблицы интервалов и prepared statement.
...
Рейтинг: 0 / 0
15.09.2014, 17:34:57
| Ответить | Цитировать | Написать  
Достать распределение
    #38747063
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
Стас0н,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select * from
(
    select site_id, floor(r / 10) stp, count(floor(r / 10)) r from
       (select site_id, count(vis_id) r from im group by 1) t1

    group by 1

) t2

order by site_id, stp desc
...
Рейтинг: 0 / 0
15.09.2014, 17:35:27
| Ответить | Цитировать | Написать  
Достать распределение
    #38747064
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
AkinaПосему только статическое формирование. Возможно, программно на основе таблицы интервалов и prepared statement.

а что, использовать field() или find_in_set() религия что ли запрещает??)))
...
Рейтинг: 0 / 0
15.09.2014, 17:37:28
| Ответить | Цитировать | Написать  
Достать распределение
    #38747112
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Lumix, то ли я неверно понимаю ТС, то ли ты. И почему-то мне кажется, что второе ближе к истине.
...
Рейтинг: 0 / 0
15.09.2014, 18:12:52
| Ответить | Цитировать | Написать  
Достать распределение
    #38747135
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
AkinaLumix, то ли я неверно понимаю ТС, то ли ты. И почему-то мне кажется, что второе ближе к истине.

Подождем, как он отреагирует на мое решение. А там видно будет...
...
Рейтинг: 0 / 0
15.09.2014, 18:36:52
| Ответить | Цитировать | Написать  
Достать распределение
    #38747153
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
Виноват, что-то бездумно скопировал запрос, не посмотрев что в нем.
Табличку
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+---------------------+-------------------+
|  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 |
+-----------+---------------------+-------------------+


Получаю запросом
Код: sql
1.
2.
3.
4.
5.
6.
7.
select adnest_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 adnest_id, count(visitor_id) desc
limit 50;



Lumix, ваше решение прогнал, но на самом деле не понял, что возвращает запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
+-----------+---------+---+
| adnest_id | stp     | r |
+-----------+---------+---+
|         8 |    9160 | 1 |
|        13 |   12429 | 1 |
|        17 |   53423 | 1 |
|        20 |       0 | 1 |
|        24 |       0 | 1 |
|        25 |       0 | 1 |


Вы бы не могли пояснить?
...
Рейтинг: 0 / 0
15.09.2014, 19:02:21
| Ответить | Цитировать | Написать  
Достать распределение
    #38747178
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
Стас0н
Lumix, ваше решение прогнал, но на самом деле не понял, что возвращает запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
+-----------+---------+---+
| adnest_id | stp     | r |
+-----------+---------+---+
|         8 |    9160 | 1 |
|        13 |   12429 | 1 |
|        17 |   53423 | 1 |
|        20 |       0 | 1 |
|        24 |       0 | 1 |
|        25 |       0 | 1 |


Вы бы не могли пояснить?

могу, если покажете каким именно запросом прогоняли, потому что тут на сцене появился какой-то adnest))) я вообще не пойму что это такое... вроде по условиям задачи у вас всего два поля: site_id, visitor_id

я же дал решение исходной задачи, не решение на тупик, в который вы зашли
...
Рейтинг: 0 / 0
15.09.2014, 19:28:21
| Ответить | Цитировать | Написать  
Достать распределение
    #38747191
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
Виноват, просто чтобы не слишком морочить голову в описании топика писал site_id (на самом деле adnest_id).
Табличка gj вашему запросу выглядит вот так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
+-----------+---------+---+
|  site_id  | stp     | r |
+-----------+---------+---+
|         8 |    9160 | 1 |
|        13 |   12429 | 1 |
|        17 |   53423 | 1 |
|        20 |       0 | 1 |
|        24 |       0 | 1 |
|        25 |       0 | 1 |
...
Рейтинг: 0 / 0
15.09.2014, 19:40:34
| Ответить | Цитировать | Написать  
Достать распределение
    #38747203
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
Судя по всему этот запрос попросту считает общее кол-во заходов пользователей на сайт.. значение последнего столбца r вообще непонятно, если честно.

Мне же нужно сделать следующее: найти распределение количества пользователей по частоте для каждого сайта. Частоту хотел взять интервалами, которые хотел бы хранить во временной таблице, которую формирую так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
create temporary table ranges (
   s int not null,
   e int not null,
   range_name varchar(15) not null
);

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

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



Дальше, если пользователь 133 раза заходил на сайт, за определенный промежуток времени, то в графе 101-200 будет +1 от этого пользователя.
Мне кажется, проще работать не с первоначальной таблицей, а с той, которую получил:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+---------------------+-------------------+
|  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 |
+-----------+---------------------+-------------------+


Здесь уже содержаться данные для каждого пользователя по количеству заходов на сайт за определенный промежуток времени
...
Рейтинг: 0 / 0
15.09.2014, 20:06:17
| Ответить | Цитировать | Написать  
Достать распределение
    #38747213
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Стас0нЗдесь уже содержаться данные для каждого пользователя по количеству заходов на сайт за определенный промежуток времения один не вижу тут (и в предыдущих примерах ТС) даже намёка на "определенный промежуток времени"?
...
Рейтинг: 0 / 0
15.09.2014, 20:25:57
| Ответить | Цитировать | Написать  
Достать распределение
    #38747215
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
Стас0нВиноват, просто чтобы не слишком морочить голову в описании топика писал site_id (на самом деле adnest_id).
Табличка gj вашему запросу выглядит вот так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
+-----------+---------+---+
|  site_id  | stp     | r |
+-----------+---------+---+
|         8 |    9160 | 1 |
|        13 |   12429 | 1 |
|        17 |   53423 | 1 |
|        20 |       0 | 1 |
|        24 |       0 | 1 |
|        25 |       0 | 1 |



ой, ну конечно я забыл двойную группировку, ведь вам ещё и сайты надо группировать
в итоге будет вот так

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select site_id, stp, r from
(
   select site_id, floor(r / 10) stp, count(floor(r / 10)) r from
      (select site_id, vis_id, count(vis_id) r from im group by 1, 2) t1
   group by 1, 2
   
) t2
order by site_id, stp
...
Рейтинг: 0 / 0
15.09.2014, 20:29:23
| Ответить | Цитировать | Написать  
Достать распределение
    #38747219
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
tanglirСтас0нЗдесь уже содержаться данные для каждого пользователя по количеству заходов на сайт за определенный промежуток времения один не вижу тут (и в предыдущих примерах ТС) даже намёка на "определенный промежуток времени"?

ТС все правильно сделал - он выкинул из примеров время, чтобы мы могли сосредоточится на объяснении ему ключевой сложности, где у него затык, а всякие фильтры по датам и пр. он всегда может докрутить опосля, после того как усвоит как же работают сводники
...
Рейтинг: 0 / 0
15.09.2014, 20:31:14
| Ответить | Цитировать | Написать  
Достать распределение
    #38747220
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Lumix, а, всё, понял, ему надо распределение по частоте, а мне почему-то показалось, что по времени...
...
Рейтинг: 0 / 0
15.09.2014, 20:32:23
| Ответить | Цитировать | Написать  
Достать распределение
    #38747223
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
Именно!
Все условия на время я уже выполнил, получилась табличка:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+---------------------+-------------------+
|  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 |
+-----------+---------------------+-------------------+



Вот теперь нужно с ней работать. Как раз таки и не допираю как распределение по частоте выбрать.
Lumix, а зачем Вы общее число заходов пользователя делите на 10 и целую часть снизу берете?
...
Рейтинг: 0 / 0
15.09.2014, 20:35:46
| Ответить | Цитировать | Написать  
Достать распределение
    #38747234
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
Lumix, вот таблица от вашего запроса:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+------+-------+
| adnest_id | stp  | r     |
+-----------+------+-------+
|         8 |    0 | 54052 |
|         8 |    1 |   632 |
|         8 |    2 |   246 |
|         8 |    3 |    40 |
|        13 |    0 | 59330 |
|        13 |    1 |  1150 |
|        13 |    2 |   370 |
|        13 |    3 |   172 |
|        13 |    4 |    83 |
|        13 |    5 |    35 |
+-----------+------+-------+


Они теперь, на сколько я понимаю, не информативна, но пусть будет для истории

Все-таки, хотелось бы понять как можно решить мою задачу с распределением и при это выводить результат не столбцами как обычно, а в строку:
Код: sql
1.
2.
3.
4.
5.
6.
    site_ID     1-10  11-20  21-30
    ----------  ---    ---     ---
    1           967    576     856
    2           576    947     847
    .
    .
...
Рейтинг: 0 / 0
15.09.2014, 20:51:55
| Ответить | Цитировать | Написать  
Достать распределение
    #38747235
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
Стас0нLumix, а зачем Вы общее число заходов пользователя делите на 10 и целую часть снизу берете?

А разве на результате выдачи вот этого запроса это не очевидно зачем?? или вы его пока по базе не прогоняли?....

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select site_id, stp, r from
(
   select site_id, floor(r / 10) stp, count(floor(r / 10)) r from
      (select site_id, vis_id, count(vis_id) r from im group by 1, 2) t1
   group by 1, 2
   
) t2
order by site_id, stp
...
Рейтинг: 0 / 0
15.09.2014, 20:53:23
| Ответить | Цитировать | Написать  
Достать распределение
    #38747236
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
Стас0нLumix, вот таблица от вашего запроса:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+------+-------+
| adnest_id | stp  | r     |
+-----------+------+-------+
|         8 |    0 | 54052 |
|         8 |    1 |   632 |
|         8 |    2 |   246 |
|         8 |    3 |    40 |
|        13 |    0 | 59330 |
|        13 |    1 |  1150 |
|        13 |    2 |   370 |
|        13 |    3 |   172 |
|        13 |    4 |    83 |
|        13 |    5 |    35 |
+-----------+------+-------+


Они теперь, на сколько я понимаю, не информативна, но пусть будет для истории

Все-таки, хотелось бы понять как можно решить мою задачу с распределением и при это выводить результат не столбцами как обычно, а в строку:
Код: sql
1.
2.
3.
4.
5.
6.
    site_ID     1-10  11-20  21-30
    ----------  ---    ---     ---
    1           967    576     856
    2           576    947     847
    .
    .



Объясните мне пожалуйста как вы посчитали число 967 и что такое 1-10?
...
Рейтинг: 0 / 0
15.09.2014, 20:55:23
| Ответить | Цитировать | Написать  
Достать распределение
    #38747237
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
Стас0нLumix, вот таблица от вашего запроса:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+------+-------+
| adnest_id | stp  | r     |
+-----------+------+-------+
|         8 |    0 | 54052 |
|         8 |    1 |   632 |
|         8 |    2 |   246 |
|         8 |    3 |    40 |
|        13 |    0 | 59330 |
|        13 |    1 |  1150 |
|        13 |    2 |   370 |
|        13 |    3 |   172 |
|        13 |    4 |    83 |
|        13 |    5 |    35 |
+-----------+------+-------+


Они теперь, на сколько я понимаю, не информативна, но пусть будет для истории
[/src]

Эта таблица показывает, что за указанный период времени на сайт №8 54 тыс. пользователей зашли от 1 до 10 раз, 632 пользователя зашли от 11 до 20 раз, 246 пользователей зашли от 21 до 30 раз и всего 40 пользователей зашли более 40 раз
...
Рейтинг: 0 / 0
15.09.2014, 20:58:00
| Ответить | Цитировать | Написать  
Достать распределение
    #38747248
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
А, только сейчас дошло, что это завуалированные интервалы с шагом 10)

На базе запускал. Результат выше. приведу на всякий пожарный еще раз:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+------+-------+
| adnest_id | stp  | r     |
+-----------+------+-------+
|         8 |    0 | 54052 |
|         8 |    1 |   632 |
|         8 |    2 |   246 |
|         8 |    3 |    40 |
|        13 |    0 | 59330 |
|        13 |    1 |  1150 |
|        13 |    2 |   370 |
|        13 |    3 |   172 |
|        13 |    4 |    83 |
|        13 |    5 |    35 |
+-----------+------+-------+



Спасибо, что помогли с этим разобраться. Понять бы еще как можно результат в строчку вытянуть.
LumixСтас0нLumix, вот таблица от вашего запроса:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+------+-------+
| adnest_id | stp  | r     |
+-----------+------+-------+
|         8 |    0 | 54052 |
|         8 |    1 |   632 |
|         8 |    2 |   246 |
|         8 |    3 |    40 |
|        13 |    0 | 59330 |
|        13 |    1 |  1150 |
|        13 |    2 |   370 |
|        13 |    3 |   172 |
|        13 |    4 |    83 |
|        13 |    5 |    35 |
+-----------+------+-------+



Они теперь, на сколько я понимаю, не информативна, но пусть будет для истории

Все-таки, хотелось бы понять как можно решить мою задачу с распределением и при это выводить результат не столбцами как обычно, а в строку:
Код: sql
1.
2.
3.
4.
5.
6.
    site_ID     1-10  11-20  21-30
    ----------  ---    ---     ---
    1           967    576     856
    2           576    947     847
    .
    .




Объясните мне пожалуйста как вы посчитали число 967 и что такое 1-10?

967 и 576 - взял с потолка, просто чтобы показать структуру, которую хочется видеть. 1-10 - это интервалы. Хотелось бы иметь не набор интервалов с шагом 10, а, например, брать из таблицы:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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);
...
Рейтинг: 0 / 0
15.09.2014, 21:10:52
| Ответить | Цитировать | Написать  
Достать распределение
    #38747260
Фотография Lumix
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lumix
Участник
Стас0н967 и 576 - взял с потолка, просто чтобы показать структуру, которую хочется видеть. 1-10 - это интервалы. Хотелось бы иметь не набор интервалов с шагом 10, а, например, брать из таблицы:


Это нечестно!!!
в ссср в пионерском лагере была такая злая шутка:

- угадай загадку
- давай
- сидит два воробья, к ним прилетел ещё один, сколько воробьев стало?
- трое!
- не правильно, один!
- WTF?!!(((
- так двое потом улетели!!

Там, где я ожидал принятие успешного решения задачи, я получил от вас чудовищную демотивацию в виде новой задачи! тем самым у меня возникло опасение, что если я и эту задачу решу, то там вместо принятия успешного решения задачи будет ещё одна новая задача или ещё какие-то новые условия, то есть вы демотивировали меня по той же самой схеме, которая изложена в сказке о рыбка и рыбке, поэтому пусть вам дальше помогает кто-нибудь другой, тут на форуме много специалистов. А я пойду залечивать свою душевную рану!!
...
Рейтинг: 0 / 0
15.09.2014, 21:30:44
| Ответить | Цитировать | Написать  
Достать распределение
    #38747325
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
Стас0н,

приведите пример исходных данных -- 5-10-15 строчек
и конкретный ожидаемый результат из ЕТИХ конкретных
исходных данных.
...
Рейтинг: 0 / 0
15.09.2014, 23:02:14
| Ответить | Цитировать | Написать  
Достать распределение
    #38747354
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
Вот пример исходных данных:
Таблица с записями о том какой пользователь зашел на сайт:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+----------------+
| site_id | visitor_id     |
+-----------+----------------+
|         8 | 14441319829303 |
|         8 | 14441319829303 |
|         8 | 14441319829303 |
|         8 | 16664540522788 |
|         8 | 31051333527785 |
|         8 | 31051333527785 |
|         8 | 43544524742951 |
|         9 | 43544524742951 |
|         9 | 59231398610260 |
|         9 | 62321349676603 |
+-----------+----------------+



Отдельно есть таблица, связывающая заход пользователя и время. Из этих данных хотелось бы получить распределения количества пользователей по частоте на каждом сайте за, например, неделю.
...
Рейтинг: 0 / 0
16.09.2014, 00:39:42
| Ответить | Цитировать | Написать  
Достать распределение
    #38747356
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
Мне кажется, первый шаг я сделал - написал запрос, который формирует таблицу с сайтом, айдишником пользователя и посещаемостью им этого сайта за определенный промежуток времени. Выглядит таблица так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+---------------------+-------------------+
|  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 |
+-----------+---------------------+-------------------+



Что-то мне подсказывает, что с такой таблице проще работать.
...
Рейтинг: 0 / 0
16.09.2014, 00:41:38
| Ответить | Цитировать | Написать  
Достать распределение
    #38747368
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
Стас0н,

приведите пример исходных данных -- 5-10-15 строчек
и конкретный ожидаемый результат (в табличном виде а не словами)
из ЕТИХ конкретных (5-10-15 строчек) исходных данных.
...
Рейтинг: 0 / 0
16.09.2014, 02:23:19
| Ответить | Цитировать | Написать  
Достать распределение
    #38748907
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
Вообще говоря исходные данные выглядят как-то так:
Таблица с записями о заходе пользователей на определенный ресурс:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
+-----------+-----------------+-------------+------+
| adnest_id | visitor_id      | timeslot_id | num  |
+-----------+-----------------+-------------+------+
|         8 | 14441319829303 |        6032 |    1 |
|         8 | 14441319829303 |        6052 |    1 |
|         6 | 14441319829303 |        6080 |    1 |
|         6 | 16664540522788 |        6032 |    9 |
|         5 | 31051333527785 |        6080 |    1 |
+-----------+----------------+-------------+------+


Есть еще табличка, которая связывает поля timeslot и реальное время захода пользователя

Из всего этого мне нужно получить распределение количества пользователей на каждом ресурсе по частоте за определенный промежуток времени.
Что-то вроде этого:
Код: sql
1.
2.
3.
4.
site_ID       1-10    11-50 51-100
    ----------  ---   ---  ---
    8           967   576   856
    6           576   947   847



где "1-10", "11-50" - это типа интервалы частоты. Так, "967" - это количество пользователей, которые посетили ресурс "8" от 1 до 10 раз за определенный промежуток времени.

Так, я написал запрос, результат которого вот такая таблица (сайт, пользователь и его количество заходов на сайт за нужный мне промежуток времени):
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+---------------------+-------------------+
|  site_id  | visitor_id          | count(visitor_id) |
+-----------+---------------------+-------------------+
|         18 |  353389751327572831 |              1120 |
|         18 | 8248747651386193561 |              1005 |
|         18 | 1773132781390152736 |               957 |
|         18 |  501663501391419490 |               936 |
|        18 | 1231874741355575425 |               925 |
|         21 | 2078329811388561752 |               904 |
|         21 | 7565480111354343102 |               875 |
|         21 |  997915071394432214 |               870 |
|         21 | 2363204321364319637 |               869 |
|         1 |   17474831359576968 |               866 |
+-----------+---------------------+-------------------+


Так, у сайта 18, будет стоять 5 в графе "1-10", а в остальных 0. И так далее
...
Рейтинг: 0 / 0
17.09.2014, 11:30:10
| Ответить | Цитировать | Написать  
Достать распределение
    #38749241
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
Стас0нВообще говоря исходные данные выглядят как-то так:
Таблица с записями о заходе пользователей на определенный ресурс:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
+-----------+-----------------+-------------+------+
| adnest_id | visitor_id      | timeslot_id | num  |
+-----------+-----------------+-------------+------+
|         8 | 14441319829303 |        6032 |    1 |
|         8 | 14441319829303 |        6052 |    1 |
|         6 | 14441319829303 |        6080 |    1 |
|         6 | 16664540522788 |        6032 |    9 |
|         5 | 31051333527785 |        6080 |    1 |
+-----------+----------------+-------------+------+


Есть еще табличка, которая связывает поля timeslot и реальное время захода пользователя

Из всего этого мне нужно получить распределение количества пользователей на каждом ресурсе по частоте за определенный промежуток времени.
Что-то вроде этого:
Код: sql
1.
2.
3.
4.
site_ID       1-10    11-50 51-100
    ----------  ---   ---  ---
    8           967   576   856
    6           576   947   847



где "1-10", "11-50" - это типа интервалы частоты. Так, "967" - это количество пользователей, которые посетили ресурс "8" от 1 до 10 раз за определенный промежуток времени.

Так, я написал запрос, результат которого вот такая таблица (сайт, пользователь и его количество заходов на сайт за нужный мне промежуток времени):
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+---------------------+-------------------+
|  site_id  | visitor_id          | count(visitor_id) |
+-----------+---------------------+-------------------+
|         18 |  353389751327572831 |              1120 |
|         18 | 8248747651386193561 |              1005 |
|         18 | 1773132781390152736 |               957 |
|         18 |  501663501391419490 |               936 |
|        18 | 1231874741355575425 |               925 |
|         21 | 2078329811388561752 |               904 |
|         21 | 7565480111354343102 |               875 |
|         21 |  997915071394432214 |               870 |
|         21 | 2363204321364319637 |               869 |
|         1 |   17474831359576968 |               866 |
+-----------+---------------------+-------------------+


Так, у сайта 18, будет стоять 5 в графе "1-10", а в остальных 0. И так далее


приведите пример исходных данных -- 5-10-15 строчек ( одна таблица )
и конкретный ожидаемый результат (в табличном виде а не словами, одна таблица )
из ЕТИХ конкретных (5-10-15 строчек) исходных данных.
...
Рейтинг: 0 / 0
17.09.2014, 15:04:15
| Ответить | Цитировать | Написать  
Достать распределение
    #38749839
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
Хорошо, вот исходные данные:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+---------------------+-------------------+
|  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 |
+-----------+---------------------+-------------------+



Результат:
Код: sql
1.
2.
3.
4.
site_ID    1-10  11-50   51-100
----------  ---   ---    ---
8           967   576    856
6           576   947    847
...
Рейтинг: 0 / 0
17.09.2014, 23:53:16
| Ответить | Цитировать | Написать  
Достать распределение
    #38749905
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
Стас0нХорошо, вот исходные данные:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+---------------------+-------------------+
|  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 |
+-----------+---------------------+-------------------+



Результат:
Код: sql
1.
2.
3.
4.
site_ID    1-10  11-50   51-100
----------  ---   ---    ---
8           967   576    856
6           576   947    847



приведите пример исходных данных -- 5-10-15 строчек (одна таблица)
и конкретный ожидаемый результат (в табличном виде а не словами, одна таблица)
из ЕТИХ КОНКРЕТНЫХ (5-10-15 строчек) исходных данных.
...
Рейтинг: 0 / 0
18.09.2014, 04:25:51
| Ответить | Цитировать | Написать  
Достать распределение
    #38750259
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
+-----------+---------------------+-------------------+
|  site_id  | visitor_id          | count(visitor_id) |
+-----------+---------------------+-------------------+
|         8 |  353389751327572831 |               112 |
|         8 | 8248747651386193561 |               100 |
|         8 | 1773132781390152736 |                95 |
|         8 |  501663501391419490 |                93 |
|         8 | 1231874741355575425 |                92 |
|         8 | 2078329811388561752 |                90 |
|         8 | 7565480111354343102 |                87 |
|         8 |  997915071394432214 |                87 |
|         8 | 2363204321364319637 |                86 |
|         8 |   17474831359576968 |                86 |
+-----------+---------------------+-------------------+



результат:
Код: sql
1.
2.
3.
site_ID    1-10  11-50  51-100  101-200
----------  ---   ---   ---       ---
8            0      0     9       1
...
Рейтинг: 0 / 0
18.09.2014, 12:42:09
| Ответить | Цитировать | Написать  
Достать распределение
    #38750528
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
Стас0н,

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
select
  z.site_id, 
  sum(z.cnt between 1 and 10) as col001_010,
  sum(z.cnt between 11 and 50) as col011_050,
  sum(z.cnt between 51 and 100) as col051_100,
  sum(z.cnt between 101 and 200) as col101_200
from
(
select
  u.site_id, 
  -- visitor_id,
  count(*) cnt
from
  user_site_visits u
group by 
  u.site_id, 
  u.visitor_id
) z
group by 
  z.site_id
...
Рейтинг: 0 / 0
18.09.2014, 15:10:04
| Ответить | Цитировать | Написать  
Достать распределение
    #38750748
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
Благодарю! Не совсем так, но я понял как нужно переделать запрос.

Скажите, а можно оформить вывод так же, только чтобы интервалы частотные хранились, например, в temporary table?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
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)
;



Просто, если нужно будет изменить интервал, то придется менять целую строку в запросе, а хочется, чтобы нужно было поменять только два числа в Temporary table - это возможно?
...
Рейтинг: 0 / 0
18.09.2014, 17:18:32
| Ответить | Цитировать | Написать  
Достать распределение
    #38750955
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
Стас0н,

напрямую -- врядли,

варинат, который я знаю --
динамически генерировать СКЛ на
клиентской стороне или в хранимой процедуре.
...
Рейтинг: 0 / 0
18.09.2014, 21:14:44
| Ответить | Цитировать | Написать  
Достать распределение
    #38750959
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
...если вы создаете всякие дрил-доуны репорты,
то, как вариант, подумайте про мулти-дименшин
репорты, ОЛАП, петахо, мсскл сервер и куча других.
...
Рейтинг: 0 / 0
18.09.2014, 21:17:33
| Ответить | Цитировать | Написать  
Достать распределение
    #38750975
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
Думаю, да, стоит посмотреть в ту сторону, как только тут закончу.

Если кому интересно, то последний вопрос по тебе здесь
...
Рейтинг: 0 / 0
18.09.2014, 21:32:17
| Ответить | Цитировать | Написать  
Достать распределение
    #38751000
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Стас0нПросто, если нужно будет изменить интервал, то придется менять целую строку в запросе, а хочется, чтобы нужно было поменять только два числа в Temporary table - это возможно?Идеального варианта нет, т.к. в MySQL в любом запросе может быть только определенное количество результирующих полей, их перечень не может меняться в процессе выполнения запроса.

Неидеальные варианты:
1) Фиксированное количество диапазонов, границы хранить в таблице.
2) Произвольное количество диапазонов, но результат получать в одном поле в виде конкатенации (см. GROUP_CONCAT).
3) Не "разворачивать" диапазоны в горизонталь в запросе, а разворачивать уже на клиенте (удобно, если в итоге получается HTML или типа того).
4) Генерировать SQL на основе данных из таблицы с границами диапазонов.
...
Рейтинг: 0 / 0
18.09.2014, 22:12:54
| Ответить | Цитировать | Написать  
Достать распределение
    #38751038
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
miksoftСтас0нПросто, если нужно будет изменить интервал, то придется менять целую строку в запросе, а хочется, чтобы нужно было поменять только два числа в Temporary table - это возможно?Идеального варианта нет, т.к. в MySQL в любом запросе может быть только определенное количество результирующих полей, их перечень не может меняться в процессе выполнения запроса.

Неидеальные варианты:
1) Фиксированное количество диапазонов, границы хранить в таблице.
2) Произвольное количество диапазонов, но результат получать в одном поле в виде конкатенации (см. GROUP_CONCAT).
3) Не "разворачивать" диапазоны в горизонталь в запросе, а разворачивать уже на клиенте (удобно, если в итоге получается HTML или типа того).
4) Генерировать SQL на основе данных из таблицы с границами диапазонов.

Я согласен тут полностью. Среди неидеальных вариантов хочется реализовать ваш п.1. Можете помочь в этом?
...
Рейтинг: 0 / 0
18.09.2014, 23:42:23
| Ответить | Цитировать | Написать  
Достать распределение
    #38751109
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Стас0нСреди неидеальных вариантов хочется реализовать ваш п.1. Можете помочь в этом?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
create temporary table tranges (
   rn int unsigned auto_increment not null
   s int not null,
   e int not null
);
insert into ranges (s, e) values <...>;

select
 z.site_id
 ,sum(t.rn=1) as cnt_1
 <...>
 ,sum(t.rn=N) as cnt_N
from
(<...>) z
join tranges t on z.cnt between t.s and t.e
group by 1

Правда, динамические названия столбцов в этом варианте не получится сделать. Придётся дополнительно дёргать таблицу диапазонов с клиента.
...
Рейтинг: 0 / 0
19.09.2014, 06:45:48
| Ответить | Цитировать | Написать  
Достать распределение
    #38751256
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
Благодарю, но проблема остается актуально - если изменится число запросов, то все-равно придется переписывать вот этот момент:
Код: sql
1.
2.
3.
4.
5.
select
 z.site_id
 ,sum(t.rn=1) as cnt_1
 <...>
 ,sum(t.rn=N) as cnt_N
...
Рейтинг: 0 / 0
19.09.2014, 10:45:53
| Ответить | Цитировать | Написать  
Достать распределение
    #38751399
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Стас0нБлагодарю, но проблема остается актуально - если изменится число запросов, то все-равно придется переписывать вот этот момент:
Код: sql
1.
2.
3.
4.
5.
select
 z.site_id
 ,sum(t.rn=1) as cnt_1
 <...>
 ,sum(t.rn=N) as cnt_N

Вы же хотели вариант по пункту 1 - вам его предложили.
Не нравится - выбирайте другой вариант.
...
Рейтинг: 0 / 0
19.09.2014, 12:28:40
| Ответить | Цитировать | Написать  
Достать распределение
    #38751413
Стас0н
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Стас0н
Гость
Виноват, подумал, что контекст предыдущих постов подскажет, что не хочется убирать/добавлять строки в случае, если нужно будет менять интервалы
...
Рейтинг: 0 / 0
19.09.2014, 12:40:54
| Ответить | Цитировать | Написать  
Достать распределение
    #38751436
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Стас0нпроблема остается актуально - если изменится число запросовкакая задача
miksoft1) Фиксированное количество диапазонов, границы хранить в таблице.такое и решение
Стас0нконтекст предыдущих постов подскажетсм.выше
...
Рейтинг: 0 / 0
19.09.2014, 12:59:49
| Ответить | Цитировать | Написать  
Достать распределение
    #38751440
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
а вообще - берёте вариант 4, пишете под него хранимку, profit
...
Рейтинг: 0 / 0
19.09.2014, 13:01:40
| Ответить | Цитировать | Написать  
43 сообщений из 43, показаны все 2 страниц
  все  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Достать распределение
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=47&tid=1834213]:
 0ms
get settings:
 8ms
get forum list:
 16ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 55ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 78ms
get tp. blocked users:
 1ms
others: 225ms
total:  399ms
созд. / загр.: 399ms
x
x
Закрыть


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