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

Откуда:
Сообщений: 602
Я уже забыл про гистограммы, но Glory вчера напомнил мне о них (тема "Получить распределение "). Поэтому я решил представить здесь два возможных решения.

Под гистограммой будем понимать https://ru.wikipedia.org/wiki/Гистограмма_(статистика).

Исходные данные:
Таблица #item содержит выборку из некоторого распределения.
Таблица @ranges содержит разбиение значений выборки на интервалы.

Результаты можно представить как вертикально, так и горизонтально.

Решение:
declare @sql varchar(8000);

if object_id(N'tempdb..#item') is not null  drop table #item;
create table #item (
 site_id     int,
 visitor_id  int   -- выборка X
);

if object_id(N'tempdb..#ranges') is not null  drop table #ranges;
create table #ranges (
 s     int,
 e     int 
);


insert #item
  select * from (values (  1,    10),
                        (  2,    20),
                        (  3,    30),
                        ( 10,   100),
                        ( 20,   200),
                        ( 40,   400),
                        (102,  1020)
                ) as T (site_id, visitor_id)

insert #ranges
  select * from (values (   1,    1),
                        (   2,   10),
                        (  11,   50),
                        (  51,  100),
                        ( 101,  200),
                        ( 201,  500),
                        ( 501, 1000),
                        (1000, 9999)
                ) as T  (   s,    e)

--вертикально
select  concat(s, '-', e) as range,  count(x.visitor_id) as num
  from       #ranges  r
  left join  #item    x on x.visitor_id between r.s and r.e
 group by r.s, r.e;

--горизонтально
set @sql = '';
select @sql=@sql+
      concat('      sum(case when visitor_id >= ', s, ' AND visitor_id <= ', e, ' then 1 else 0 end) as [',concat(s,'-',e), '],',char(10))
  from #ranges;
set @sql=concat('select ', char(10), substring(@sql,1,len(@sql)-2), char(10),'  from #item', char(10))

print @sql;

execute (@sql);


Результат вертикально:
range	   num
1-1 0
2-10 1
11-50 2
51-100 1
101-200 1
201-500 1
501-1000 0
1000-9999 1

Результат горизонтально:
1-1  2-10  11-50  51-100  101-200  201-500  501-1000  1000-9999
0 1 2 1 1 1 0 1

Во втором случае формируется запрос:
select 
      sum(case when visitor_id >= 1 and visitor_id <= 1 then 1 else 0 end) as [1-1],
      sum(case when visitor_id >= 2 and visitor_id <= 10 then 1 else 0 end) as [2-10],
      sum(case when visitor_id >= 11 and visitor_id <= 50 then 1 else 0 end) as [11-50],
      sum(case when visitor_id >= 51 and visitor_id <= 100 then 1 else 0 end) as [51-100],
      sum(case when visitor_id >= 101 and visitor_id <= 200 then 1 else 0 end) as [101-200],
      sum(case when visitor_id >= 201 and visitor_id <= 500 then 1 else 0 end) as [201-500],
      sum(case when visitor_id >= 501 and visitor_id <= 1000 then 1 else 0 end) as [501-1000],
      sum(case when visitor_id >= 1000 and visitor_id <= 9999 then 1 else 0 end) as [1000-9999]
  from #item
7 окт 14, 14:49    [16671431]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Glory
Member

Откуда:
Сообщений: 104751
Внезапно
declare @sql varchar(max);

if object_id(N'tempdb..#item') is not null  drop table #item;
create table #item (
 site_id     int,
 visitor_id  int   -- выборка X
);

if object_id(N'tempdb..#ranges') is not null  drop table #ranges;
create table #ranges (
 s     int,
 e     int 
);


insert #item
  select * from (values (  1,    10),
                        (  2,    20),
                        (  3,    30),
                        ( 10,   100),
                        ( 20,   200),
                        ( 40,   400),
                        (102,  1020)
                ) as T (site_id, visitor_id)

insert #ranges
select top (5000) row_number() over(order by a.name) as s, row_number() over(order by a.name)+9 as e
from 
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

--вертикально
select  concat(s, '-', e) as range,  count(x.visitor_id) as num
  from       #ranges  r
  left join  #item    x on x.visitor_id between r.s and r.e
 group by r.s, r.e;

--горизонтально
set @sql = '';
select @sql=@sql+
      concat('      sum(case when visitor_id >= ', s, ' AND visitor_id <= ', e, ' then 1 else 0 end) as [',concat(s,'-',e), '],',char(10))
  from #ranges;
set @sql=concat('select ', char(10), substring(@sql,1,len(@sql)-2), char(10),'  from #item', char(10))

print @sql;

execute (@sql);
7 окт 14, 15:41    [16671834]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Wlr-l
Member

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

Да, внезапно, благодаря Вам!

Кстати, к вопросу, что "гистограмму нельзя отобразить средствами T-SQL".
if object_id(N'tempdb..#item') is not null  drop table #item;
create table #item (
 site_id     int,
 visitor_id  int 
);

if object_id(N'tempdb..#ranges') is not null  drop table #ranges;
create table #ranges (
 s     int,
 e     int 
);


insert #item
  select * from (values (  1,    10),
                        (  2,    20),
                        (  3,    30),
                        ( 10,   100),
                        ( 20,   200),
                        ( 40,   400),
                        (102,  1020)
                ) as T (site_id, visitor_id);

insert #ranges
  select * from (values (   1,    1),
                        (   2,   10),
                        (  11,   50),
                        (  51,  100),
                        ( 101,  200),
                        ( 201,  500),
                        ( 501, 1000),
                        (1000, 9999)
                ) as T  (   s,    e);

with A as (
  select  concat(s, '-', e) as range,  count(x.visitor_id) as num
    from       #ranges  r
    left join  #item    x on x.visitor_id between r.s and r.e
   group by r.s, r.e
)

select *, replicate('*',num) as gstgr
  from A;


Результат:
range      num  gstgr
1-1 0
2-10 1 *
11-50 2 **
51-100 1 *
101-200 1 *
201-500 1 *
501-1000 0
1000-9999 1 *

Так делали, когда были алфавитно-цифровые дисплее и принтеры.
7 окт 14, 16:13    [16672056]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Glory
Member

Откуда:
Сообщений: 104751
Wlr-l
Так делали, когда были алфавитно-цифровые дисплее и принтеры.

Извините, а вы реально думаете, что это сервер вам отображает ?
7 окт 14, 16:16    [16672069]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Glory
Member

Откуда:
Сообщений: 104751
И еще вопрос
Можено теперь любой запрос, который сформирует несколько полей - частично символьных, частично числовых - , называть гистограммой ?
Вот это вот гистограмма ?
SELECT a.City, COUNT(bea.AddressID) EmployeeCount
FROM Person.BusinessEntityAddress bea 
    INNER JOIN Person.Address a
        ON bea.AddressID = a.AddressID
GROUP BY a.City
ORDER BY a.City;


а это ?

USE AdventureWorks2008R2;
GO
SELECT DATEPART(yyyy,OrderDate) AS N'Year'
    ,SUM(TotalDue) AS N'Total Order Amount'
FROM Sales.SalesOrderHeader
GROUP BY DATEPART(yyyy,OrderDate)
ORDER BY DATEPART(yyyy,OrderDate);
7 окт 14, 16:23    [16672124]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Wlr-l
Member

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

Я думаю, что дисплей, в конечном итоге, отображает данные. Но всю основную работу сделал "сервер".
7 окт 14, 16:25    [16672138]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Glory
Member

Откуда:
Сообщений: 104751
Wlr-l
Но всю основную работу сделал "сервер".

А сервер знает, что он сделал именно гистограмму ?
7 окт 14, 16:26    [16672146]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Wlr-l
Member

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

"Можено теперь любой запрос, который сформирует несколько полей - частично символьных, частично числовых - , называть гистограммой?"

Ссылка на то, что считать гистограммой дана.
7 окт 14, 16:28    [16672159]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Glory
Member

Откуда:
Сообщений: 104751
Wlr-l
Ссылка на то, что считать гистограммой дана.

Т.е. вы тоже можете ответить на заданные вопросы ?
7 окт 14, 16:31    [16672183]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Wlr-l
Member

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

"А сервер знает, что он сделал именно гистограмму ?"

Спросите у него.
7 окт 14, 16:33    [16672204]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Glory
Member

Откуда:
Сообщений: 104751
Wlr-l
Glory,

"А сервер знает, что он сделал именно гистограмму ?"

Спросите у него.

Понятно.
Мопед - не мой, я только объяву разметил.
7 окт 14, 16:34    [16672217]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Wlr-l
Member

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

Сервер состоит из процессора, памяти, шин, ... А знает ли процессор, что он выполняет команды SQL Server?
7 окт 14, 16:45    [16672344]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Glory
Member

Откуда:
Сообщений: 104751
Wlr-l
Сервер состоит из процессора, памяти, шин, ... А знает ли процессор, что он выполняет команды SQL Server?

Что же вы так про гистрограммы то ответить не можете ?
Когда мой произвольный запрос с GROUP BY и SUM превращается в гистограмму то ?
После прочтения ему статьи из Вики ? После того, как процессор выполнит первую/последнюю операцию ?
7 окт 14, 16:49    [16672370]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Wlr-l
Member

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

Я рассказал SQL Server на доступном ему языке, что нужно сделать, чтобы на дисплее появилась картинка, типа

*
***
**

******

Вы это не заметили. И, как всегда, увели разговор в другую сторону.
7 окт 14, 16:53    [16672408]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Glory
Member

Откуда:
Сообщений: 104751
Wlr-l
Вы это не заметили. И, как всегда, увели разговор в другую сторону.

Мне просто интересно, в чем смысл называть ваш частично работающий скрипт "гистограммой" ?
Когда у него уже есть другое более известное имя - перекрестнывй запрос.
7 окт 14, 16:55    [16672428]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Wlr-l
Member

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

Потому что гистограмма - это цель.
Перекрестный запрос - это способ достижения цели.
7 окт 14, 17:02    [16672465]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Wlr-l
Member

Откуда:
Сообщений: 602
Wlr-l,
Иными словами гистограмма - это семантика, а перекрестный запрос - грамматика и синтаксис.
7 окт 14, 17:03    [16672472]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Glory
Member

Откуда:
Сообщений: 104751
Wlr-l
Иными словами гистограмма - это семантика, а перекрестный запрос - грамматика и синтаксис.

Т.е. можно один и тот же синтаксис называть по разному и публиковать как независимое решение ?
А для чего ?
7 окт 14, 17:09    [16672514]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
Glory
Мне просто интересно, в чем смысл называть ваш частично работающий скрипт "гистограммой" ?
Когда у него уже есть другое более известное имя - перекрестнывй запрос.


может быть человек занимается статистикой и для него гистограмма имеет определение из этой области

Гистограмма:
Гистограмма — способ графического представления табличных данных.
Гистограмма (статистика) — это функция, приближающая плотность вероятности некоторого распределения, построенная на основе выборки из него.
Гистограмма (фотография) — это график распределения полутонов изображения, в котором по горизонтальной оси представлена Яркость, а по вертикали — относительное число пикселов с данным значением яркости.
7 окт 14, 17:13    [16672539]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Wlr-l
Member

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

Точно также "Сегодня теплая погода". Для одних людей это будет истинно, а для других ложно.
Уточним "Сегодня 5 градусов тепла". Для человека, живущего в Европе это может быть истинно, а для человека, живущего в Америке это может быть ложно.

Предложение одно, а смысл разный.
7 окт 14, 17:16    [16672552]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Glory
Member

Откуда:
Сообщений: 104751
a_voronin
может быть человек занимается статистикой и для него гистограмма имеет определение из этой области

Да ради Бога.
Просто интересно, чем собирание марок отличается от собирания монет, если и то и другое называется коллекционированием ?
7 окт 14, 17:18    [16672566]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Glory
Member

Откуда:
Сообщений: 104751
Wlr-l
Предложение одно, а смысл разный.

Т.е. ваш скрипт еще и разные по смыслу результаты выдает разным людям ? Или процессорам ?
7 окт 14, 17:20    [16672578]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4901
Glory
Просто интересно, чем собирание марок отличается от собирания монет, если и то и другое называется коллекционированием ?


Весом коллекции
7 окт 14, 17:20    [16672582]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Wlr-l
Member

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

Да!!!

select * from table


Все остальное дело клиента!!!
7 окт 14, 17:21    [16672592]     Ответить | Цитировать Сообщить модератору
 Re: Гистограмма  [new]
Glory
Member

Откуда:
Сообщений: 104751
a_voronin
Glory
Просто интересно, чем собирание марок отличается от собирания монет, если и то и другое называется коллекционированием ?


Весом коллекции

А "собирание"(процесс) какое отношение имеет к весу коллекции ?
7 окт 14, 17:21    [16672594]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить