Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Запрос группировка данных / 14 сообщений из 14, страница 1 из 1
12.07.2019, 10:02
    #39836677
AidarH
AidarH
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос группировка данных
Есть следующие данные:
Код: plsql
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.
with base as (select 11 as id, 
                     'Казань' as city , 
                     1 as curr, -- тек 
                     0 as prev -- пред 
                     from dual
union all
select 11, 'Казань', 1, 0 from dual
union all
select 12, 'Буинск', 1, 0 from dual
union all
select 12, 'Буинск', 1, 0 from dual
union all
select 12, 'Буинск', 1, 0 from dual
union all
select 12, 'Буинск', 0, 1 from dual
union all
select 12, 'Буинск', 0, 1 from dual
union all
select 13, 'Заинск', 1, 0 from dual
union all
select 13, 'Заинск', 1, 0 from dual
union all
select 13, 'Заинск', 0, 1 from dual
union all
select 15, 'Томск', 1, 0 from dual
union all
select 15, 'Томск', 0, 1 from dual
)
select * 
       from base



Необходимо получить следующий набор данных

Наименование Количество
Казань 2
Буинск 3
Заинск 2
Города кроме томска 7
Города пред кроме томска 3
Томск 1
Томск пред 1
Все города 8
Все города пред 4

Как можно сделать запрос с помощью аналитических функций без лишних union all?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.07.2019, 10:11
    #39836686
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос группировка данных
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
select 'Наименование Количество
Казань 2
Буинск 3
Заинск 2
Города кроме томска 7
Города пред кроме томска 3
Томск 1
Томск пред 1
Все города 8
Все города пред 4' from dual;

http://www.bugtraq.ru/forum/faq/general/smart-questions.html] RTFM
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.07.2019, 10:13
    #39836687
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос группировка данных
AidarH,

Что Томск делает в списке Татарстанских городов?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.07.2019, 10:15
    #39836688
AidarH
AidarH
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос группировка данных
AmKad,

Это просто для примера.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.07.2019, 10:15
    #39836689
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос группировка данных
AidarHКак можно сделать запрос с помощью аналитических функций без лишних union all?Аналитические функции не генерируют новых строк.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.07.2019, 10:20
    #39836692
AidarH
AidarH
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос группировка данных
Elic,

А как можно тогда с помощью rollup или cube или группировок?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.07.2019, 10:24
    #39836693
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос группировка данных
AidarHА как можно тогда с помощью rollup или cube или группировок?Не имеет смысла, пока задача не формализована.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.07.2019, 11:26
    #39836732
AmKad
AmKad
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос группировка данных
AidarHЭто просто для примера.Елабугу добавь
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.07.2019, 18:29
    #39836984
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос группировка данных
Что-то типа:

Код: plsql
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.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
with base as (select 11 as id,
                     'Kazan' as city ,
                     1 as curr, -- ???
                     0 as prev -- ????
                     from dual
union all
select 11, 'Kazan', 1, 0 from dual
union all
select 12, 'Buinsk', 1, 0 from dual
union all
select 12, 'Buinsk', 1, 0 from dual
union all
select 12, 'Buinsk', 1, 0 from dual
union all
select 12, 'Buinsk', 0, 1 from dual
union all
select 12, 'Buinsk', 0, 1 from dual
union all
select 13, 'Zainsk', 1, 0 from dual
union all
select 13, 'Zainsk', 1, 0 from dual
union all
select 13, 'Zainsk', 0, 1 from dual
union all
select 15, 'Tomsk', 1, 0 from dual
union all
select 15, 'Tomsk', 0, 1 from dual
)
select  case grouping(city) + group_id()
          when 1 then 'Tomsk prev'
          when 2 then 'Cities except Tomsk'
          when 3 then 'Cities prev except Tomsk'
          when 4 then 'All cities'
          when 5 then 'All cities prev'
          else city
        end city,
        case grouping(city) + group_id()
          when 1 then sum(case city when 'Tomsk' then prev end)
          when 2 then sum(case city when 'Tomsk' then 0 else curr end)
          when 3 then sum(case city when 'Tomsk' then 0 else prev end)
          when 5 then sum(prev)
          else sum(curr)
        end cnt
  from  base
  group by grouping sets ((),(),(),(),(),(city))
  order by group_id(),
           max(id)
/

CITY                            CNT
------------------------ ----------
Kazan                             2
Buinsk                            3
Zainsk                            2
Tomsk                             1
Tomsk prev                        1
Cities except Tomsk               7
Cities prev except Tomsk          3
All cities                        8
All cities prev                   4

9 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.07.2019, 18:48
    #39836995
mdbelov
mdbelov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос группировка данных
SY, but wrong row order.
подгон решения под ответ:
Код: plsql
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.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
WITH
   base AS
      (SELECT 11 AS id, 'Казань' AS city, 1 AS curr, 0 AS prev FROM DUAL
       UNION ALL
       SELECT 11, 'Казань', 1, 0 FROM DUAL
       UNION ALL
       SELECT 12, 'Буинск', 1, 0 FROM DUAL
       UNION ALL
       SELECT 12, 'Буинск', 1, 0 FROM DUAL
       UNION ALL
       SELECT 12, 'Буинск', 1, 0 FROM DUAL
       UNION ALL
       SELECT 12, 'Буинск', 0, 1 FROM DUAL
       UNION ALL
       SELECT 12, 'Буинск', 0, 1 FROM DUAL
       UNION ALL
       SELECT 13, 'Заинск', 1, 0 FROM DUAL
       UNION ALL
       SELECT 13, 'Заинск', 1, 0 FROM DUAL
       UNION ALL
       SELECT 13, 'Заинск', 0, 1 FROM DUAL
       UNION ALL
       SELECT 15, 'Томск', 1, 0 FROM DUAL
       UNION ALL
       SELECT 15, 'Томск', 0, 1 FROM DUAL)
  SELECT 
         CASE
            WHEN GROUPING_ID (b.id, b.city) = 0 THEN b.city
            WHEN GROUPING_ID ('Города кроме томска') = 0 THEN 'Города кроме томска'
            WHEN GROUPING_ID ('Все города пред') = 0 THEN 'Все города пред'
            WHEN GROUPING_ID ('Города пред кроме томска') = 0 THEN 'Города пред кроме томска'
            WHEN GROUPING_ID ('Томск пред') = 0 THEN 'Томск пред'
            WHEN GROUPING_ID ('Все города') = 0 THEN 'Все города'
         END
            "Наименование",
         CASE
            WHEN GROUPING_ID (b.id, b.city) = 0 THEN SUM (curr)
            WHEN GROUPING_ID ('Города кроме томска') = 0 THEN SUM (DECODE (b.city, 'Томск', 0, curr))
            WHEN GROUPING_ID ('Все города пред') = 0 THEN SUM (DECODE (b.prev, 1, 1, 0))
            WHEN GROUPING_ID ('Города пред кроме томска') = 0 THEN SUM (DECODE (b.prev, 1, DECODE (b.city, 'Томск', 0, 1), 0))
            WHEN GROUPING_ID ('Томск пред') = 0 THEN SUM (DECODE (b.prev, 1, DECODE (b.city, 'Томск', 1, 0), 0))
            WHEN GROUPING_ID ('Все города') = 0 THEN SUM (curr)
         END
            "Количество"
    FROM base b
GROUP BY GROUPING SETS ((b.id, b.city),
                        ('Города кроме томска'),
                        ('Города пред кроме томска'),
                        ('Томск пред'),
                        ('Все города'),
                        ('Все города пред'))
ORDER BY NVL (
            b.id,
            CASE "Наименование"
               WHEN 'Томск пред' THEN 15
               WHEN 'Города кроме томска' THEN 14
               WHEN 'Города пред кроме томска' THEN 14
               WHEN 'Все города' THEN 16
               WHEN 'Все города пред' THEN 17
            END),
          "Наименование"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.07.2019, 19:09
    #39836998
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос группировка данных
mdbelovSY, but wrong row order.


Ну и в чем проблема:

Код: plsql
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.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
with base as (select 11 as id,
                     'Kazan' as city ,
                     1 as curr, -- ???
                     0 as prev -- ????
                     from dual
union all
select 11, 'Kazan', 1, 0 from dual
union all
select 12, 'Buinsk', 1, 0 from dual
union all
select 12, 'Buinsk', 1, 0 from dual
union all
select 12, 'Buinsk', 1, 0 from dual
union all
select 12, 'Buinsk', 0, 1 from dual
union all
select 12, 'Buinsk', 0, 1 from dual
union all
select 13, 'Zainsk', 1, 0 from dual
union all
select 13, 'Zainsk', 1, 0 from dual
union all
select 13, 'Zainsk', 0, 1 from dual
union all
select 15, 'Tomsk', 1, 0 from dual
union all
select 15, 'Tomsk', 0, 1 from dual
)
select  case grouping(city) + group_id()
          when 1 then 'Cities except Tomsk'
          when 2 then 'Cities prev except Tomsk'
          when 3 then 'Tomsk prev'
          when 4 then 'All cities'
          when 5 then 'All cities prev'
          else city
        end city,
        case grouping(city) + group_id()
          when 1 then sum(case city when 'Tomsk' then 0 else curr end)
          when 2 then sum(case city when 'Tomsk' then 0 else prev end)
          when 3 then sum(case city when 'Tomsk' then prev end)
          when 5 then sum(prev)
          else sum(curr)
        end cnt
  from  base
  group by grouping sets ((),(),(),(),(),(city))
  order by case city
             when 'Tomsk' then 1.1
             else group_id()
           end,
           nvl2(city,max(id),null) nulls last
/

CITY                            CNT
------------------------ ----------
Kazan                             2
Buinsk                            3
Zainsk                            2
Cities except Tomsk               7
Cities prev except Tomsk          3
Tomsk                             1
Tomsk prev                        1
All cities                        8
All cities prev                   4

9 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
12.07.2019, 19:59
    #39837004
mdbelov
mdbelov
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос группировка данных
SYmdbelovSY, but wrong row order.


Ну и в чем проблема:


Нет проблемы, твой вариант теперь 146 из 100! Я забыл про group_id() и получился этот пятничный ужас.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.07.2019, 07:45
    #39837050
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос группировка данных
mdbelovтвой вариант теперь 146 из 100!А на самом деле - страшный разовый говнокод, потому что
Elicзадача не формализована.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.07.2019, 08:52
    #39837314
AidarH
AidarH
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос группировка данных
Всем, кто принимал участие спасибо за помощь. Доброго дня и хорошего настроения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Запрос группировка данных / 14 сообщений из 14, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 170ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1882307]:
 0ms
get settings:
 11ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 45ms
get topic data:
 14ms
get forum data:
 3ms
get page messages:
 56ms
get tp. blocked users:
 2ms
others: 17ms
total:  170ms
x
x
Закрыть


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