Oracle SQL

Фильтр по тегу: group by


Конструкция KEEP FIRST/LAST в SQL за 5 минут, ясно и понятно

Конструкция KEEP FIRST/LAST используется в SQL Oracle для вычисления значения, первой или последней записи в заданной подгруппе, отcортированной по некоторому признаку
она так же позволяет найти результат агрегатной функции по сгруппированным данным, если таких значений несколько

Проще понять это на примере
создадим таблицу курсов валют

CREATE TABLE prices
( 
 ticker VARCHAR2(3), -- валюта сокращ
 pdate DATE, -- дата 
 price FLOAT -- курс валюты
);

заполним таблицу тестовыми данными
за каждый день в таблице может быть несколько курсов заданной валюты
читать дальше...
добавлено: 02 окт 17 просмотры: 1438, комментарии: 0



Подсчитать сколько воскресений , понедельников , суббот и тд в заданном году

Авторский курс. SQL от новичка до профессионала. Бесплатное вводное занятие. Сертификат. Записывайся!
Прокачаю до уровня БОГ!


Подсчитать количество дней недели в заданном году
то есть сколько было в указанном году понедельников вторников воскресений ...
можно следующим образом :
   select count(1) dwcount,
          to_char(to_date('01/01/1940', 'DD/MM/YYYY') + level - 1, 'DAY') daywe -- 1940 это год , который нам нужен
     from dual
   connect by level <
              to_number(to_char(Trunc(To_Date(1940 + 1, 'YYYY'), 'YYYY') - 1, -- 1940 это год , который нам нужен
                                'ddd')) -- считаем колч дней 
    group by to_char(to_date('01/01/1940', 'DD/MM/YYYY') + level - 1, 'DAY'); -- группируем запрос по дням

один читатель, предложил свое решение, более рациональное и эффективное, подвергнув мое решение справедливой критике, публикую
  with t as (select to_date('31/12/'||'1940','dd/mm/yyyy') fd_year from dual)
    select to_char(t.fd_year-level+1,'DAY') fv_day,count(1) fn_count from t
    connect by level <= to_number(to_char(t.fd_year,'ddd'))
  group by to_char(t.fd_year-level+1,'DAY')
добавлено: 20 мар 17 просмотры: 3233, комментарии: 1



Превращаем столбцы в строчки LISTAGG

Авторский курс. SQL от новичка до профессионала. Бесплатное вводное занятие. Сертификат. Записывайся!
Прокачаю до уровня БОГ!


-- Превращаем столбцы в строчки
-- функция LISTAGG
-
Описание функции listagg
согласно переводу Oracle Reference Database listagg может использоваться
1) Как одиночная агрегатная функция, LISTAGG обрабатывает все строки и возвращает одно значение.
2) Как групповая агрегатная функция, LISTAGG обрабатывает и возвращает данные для каждой группы определенной в GROUP BY.
3) Как аналитическая функция, LISTAGG обрабатывает данные разбитые на блоки, задаваемые одним или несколькими выражениями query_partition_clause.
давайте рассмотрим самый интересный пример:
создадим таблицу следующего вида
имя клиента , номер телефона клиента

по ТЗ у каждого клиента может быть несколько телефонных номеров

подготовим тестовые данные
create table cl_phones(tname varchar2(100), phone varchar2(30));
insert into cl_phones(tname,phone) values ('Андрей','867-843-25');
insert into cl_phones(tname,phone) values ('Андрей','830-044-35');
insert into cl_phones(tname,phone) values ('Андрей','530-055-35');
insert into cl_phones(tname,phone) values ('Максим','530-055-35');
insert into cl_phones(tname,phone) values ('Максим','555-011-35');
insert into cl_phones(tname,phone) values ('Ваня','530-055-31');
insert into cl_phones(tname,phone) values ('Ваня','531-051-32');
insert into cl_phones(tname,phone) values ('Ваня','532-052-33');
insert into cl_phones(tname,phone) values ('Ваня','533-053-35');


-- давайте напишем запрос который бы выводил имя , телефоны в строку с разделителем
SELECT
   tname,
   LISTAGG(phone, ' ; ') WITHIN GROUP (ORDER BY phone) phonestr
FROM cl_phones
GROUP BY tname


результат
tname phonestr
Андрей 530-055-35 ; 830-044-35 ; 867-843-25
Ваня 530-055-31 ; 531-051-32 ; 532-052-33 ; 533-053-35
Максим 530-055-35 ; 555-011-35


необходимо отметить что функция LISTAGG появилась только в версии 11.2
Чалышев М.М www.orasource.ru
добавлено: 13 май 13 просмотры: 20672, комментарии: 6