Информация

Последние записи

Теги


Блоги


Записи из всех блогов на Sql.ru с тегом: select


Sum по disitnct полю , в другой колонке

Блог: Oracle SQL
Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA
-- итак требуется найти сумму по distinct полю на основе идентификатора, который находится в другой колонке
-- е1сть доп условие нельзя использовать подзапрос, то есть необходимо выполнить задачу за одно обращение


-- подготовим данные для тестового примера
drop table t; -- если надо
create table t as
WITH r AS (
select /*+ materialize */ * from ALL_OBJECTS WHERE ROWNUM < 100 )
select object_id as id,length(r.object_name) as fid,
 (select count(1) from r a where length(r.object_name) = length(a.object_name))  as price   
from r


-- есть вторичный ключ fid от которого зависит значение в поле price
-- необходимо найти сумму по полю price в distinct поля fid одним запросом, то есть без вложенного select

select sum(price) sm from t 
-- это неправильное решение

select sum(distinct price) sm from t 
-- и это неправильное решение price может быть одинаковый для разных fid
-- надо как то так
select sum(price distinct fid) sm from t
-- но так написать нельзя

-- первое решение,
-- работает быстро, но решение не идеально, ключ может быть и не числовой
-- сумма Id + price - сумма id
-- , во избежание неприятностей использовали to_number(rpad(fid, 20)
select sum(distinct to_number(rpad(fid,10,'0')) +price) -  sum(distinct to_number(rpad(fid,10,'0'))) as sm  from t


-- решение второе - используем model
  select r from  t 
   model
   return updated rows   
   dimension by(id)
  measures(0 r, nullif(price, lag(price)over(partition by fid order by id)) lg)
  rules upsert(r[0] = sum(lg)[any])
автор: Myp3_u_K добавлено: 23 май 18 просмотры: 2935, комментарии: 1



Ретроспективные запросы и корзина ORACLE

Блог: Oracle SQL
Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA

А что если нам необходимо узнать , какие данные были в раньше до выполнения операций модификации данных....
как это сделать ?,
Для этого в Oracle предусмотрен механизм ретроспективных запросов (Flashback Query);
То есть, мы можем с помощью запросов посмотреть в прошлое.
Пример подобного запроса
для примера нам понадобится таблица
create table obj_t -- table 
as select owner, object_type  from all_objects group by owner, object_type;
;

запомним время создания таблицы 17:01:01

delete obj_t where object_type = 'TABLE';

удалим наименования таблиц
и запомним время 17:10:14

delete obj_t where owner in ('SYS', 'SYSTEM');

удалим наименования таблиц
и запомним время и запомним время 17:11:22

select *
  from obj_t ;

нет таблиц , нет обьектов owner = sys system

select *
  from obj_t as of scn timestamp_to_scn(to_timestamp('19/04/2018 17:10:00','DD/MM/YYYY HH24:MI:SS')) ;

есть таблицы, нет обьектов owner = sys system

select *
  from obj_t as of scn timestamp_to_scn(to_timestamp('19/04/2018 17:11:00','DD/MM/YYYY HH24:MI:SS')) ;

видим обьекты owner = sys system
читать дальше...
автор: Myp3_u_K добавлено: 17 май 18 просмотры: 4487, комментарии: 0



Конструкция With и Function

Блог: Oracle SQL
Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA

В SQL диалекте Oracle 12C есть возможность определить функцию или процедуру на языке PL/SQL с помощью оператора WITH, используя обычный SQL


Синаксис
WITH
 PROCEDURE <NAME_PROCEDURE> 
 BEGIN
 ... 
 END;

 FUNCTION <NAME_FUNCTION>
 BEGIN
   ...
 END;
SELECT <NAME_FUNCTION>
FROM <TABLE>;


Примеры
Вывести на экран тип объекта Перевернуть слова означающие типы объектов в ALL_OBJECTS, ограничить выборку 100 строками
WITH
  FUNCTION reversive_fnc(p_name VARCHAR2) RETURN VARCHAR2
  is i NUMBER; v VARCHAR2(50);
  begin
    FOR i IN 1..LENGTH(p_name) LOOP
      v := v || SUBSTR(p_name, LENGTH(p_name)-i+1, 1);
    END LOOP; 
    return v; 
  end;
SELECT DISTINCT reversive_fnc(object_type) as rname, object_type FROM all_objects WHERE rownum < 101;


Добавить к идентификатору объекта заданное кол нулей , преобразовать к числу, ограничить выборку 100 строками
WITH
  FUNCTION incid_fnc(p_id NUMBER, p_count NUMBER) RETURN NUMBER
  is
  begin     
    return TO_NUMBER(rpad(p_id , p_count, '0')); 
  end;
SELECT object_id, incid_fnc(object_id, 10) fn FROM all_objects WHERE rownum < 101
автор: Myp3_u_K добавлено: 02 май 18 просмотры: 4908, комментарии: 1



WITH в ORACLESQL

Блог: Oracle SQL
Чалышев Максим Михайлович
SQL. 5 дней которые изменят твою жизнь.
Моя книга, электронная версия бесплатно
Oracle SQL. 100 шагов от новичка до профессионала. 20 дней новых знаний и практики или платная печатная версия

Бесплатные авторские видеокурсы SQL, PLSQL, JAVA
Оператор WITH позволяет заранее формировать внутренний подзапрос , позволяет обращаться к данному подзапросу по синониму в основном запросе.
Синтаксис
WITH 
T1 as (SELECT field_list FROM T list join WHERE cond group by ...) , 
T2 as (SELECT field_list FROM T list join WHERE cond2 group by ..) , tn as ....
SELECT * FROM T1, T2 where t1.cond= t2.cond

и для того чтобы было совсем понятно - пара примеров :)

WITH T AS (SELECT * FROM ALL_OBJECTS) , T2 AS 
(SELECT * FROM ALL_tables A , T WHERE T.OBJECT_NAME = A.TABLE_NAME ) 
SELECT TABLE_NAME FROM T2 
 
-- сложное обращение 

WITH T AS (SELECT * FROM ALL_OBJECTS) , 
T2 AS (SELECT * FROM ALL_tables) 
SELECT * FROM T, T2 WHERE T.OBJECT_NAME = T2.TABLE_NAME;
 
-- последовательно используем t1,t2

>Splash
>А если перед основным запросом два из WITH, притом что у второго используется первый, а потом они все вместе идут в основной?
with 
t1 as (select object_type, created lstcrdt  , object_name from all_objects),
t2 as (select object_type, count(object_name) cnttype from t1 group by object_type)
select * from t1 inner join t2 on t1.object_type = t2.object_type
автор: Myp3_u_K добавлено: 21 ноя 17 просмотры: 23795, комментарии: 4



Oracle 12c и JSON немножечко...

Блог: Oracle SQL
SQL. 5 дней которые изменят твою жизнь.

Бесплатные авторские видеокурсы SQL, PLSQL, JAVA
Чалышев Максим Михайлович

немного про JSON и ORACLE 12
create table info_user_v (
  id number primary key -- иднтификатор уникальный
, name varchar2(50) -- наименование 
, json_data varchar2(4000) -- данные JSON 
);


в oracle 12 есть специальное ограничение, чтобы добавлять в поле только данные json формата, и чтобы исключить ошибки со структурой JSON
добавим его к нашей таблице
alter table info_user_v add constraint c_1_json_data check(json_data is json);


читать дальше...
автор: Myp3_u_K добавлено: 05 окт 17 просмотры: 9578, комментарии: 0



USE HINT и ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES

В одной из предыдущих заметок мы говорили о таком механизме как Cardinality Estimator.
www.queryprocessor.ru
Cardinality Estimation, СЕ (оценка кардинальности) – это оценка предполагаемого числа строк, которое будет обработано тем или иным оператором запроса. Оценка – один из ключевых факторов при построении плана запроса. Оценку числа строк осуществляет компонент Cardinality Estimator.

Хинт ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES контролирует один из аспектов поведения этого компонента, а именно, оценку комплексных предикатов. На сегодняшний день SQL Server имеет три алгоритма оценки подобных предикатов:

  • оценка по предположению независимости;
  • оценка по минимальной селективности;
  • оценка по алгоритму exponential backoff.

Удобнее всего будет разобрать их на примере.

Читать дальше...
автор: SomewhereSomehow добавлено: 06 мар 17 просмотры: 2812, комментарии: 0



MODEL. SQL высший пилотаж. Часть 2. Сложные последовательности.

Блог: Oracle SQL
Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA

Используем Model для создания массивов
-- создание одномерного массива
SELECT *
FROM dual
    MODEL DIMENSION BY (0 as т1)
    MEASURES (cast(dummy as varchar2(20)) as ct) 
    RULES (        
        ct[0] = '1',
        ct[1] = '2',       
        ct[1] = '3',
        ct[2] = '4',
        ct[3] = '5'
    ) order by 1;
--Т1	CT
--0	1
--1	3
--2	4
--3	5


читать дальше...
автор: Myp3_u_K добавлено: 03 июл 15 просмотры: 5409, комментарии: 0



Конструкция MERGE, вставка - обновление

Блог: Oracle SQL
Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA

Моя книга, электронная версия бесплатно
Oracle SQL. 100 шагов от новичка до профессионала. 20 дней новых знаний и практики или платная печатная версия

Назначение
Позволяет дополнять и обновлять данные одной таблицы - данными другой таблицы. При слиянии таблиц проверяется условие, и если оно истинно, то выполняется Update, а если нет - Insert. Причем нельзя изменять поля таблицы в секции Update, по которым идет связывание двух таблиц.

Является командой DML!

Синтаксис
MERGE INTO TABLE_NAME 
USING table_reference ON (condition) WHEN MATCHED 
THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED 
THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...) ;

читать дальше...
автор: Myp3_u_K добавлено: 17 апр 15 просмотры: 90405, комментарии: 2



Аналитический SQL Oracle за 20 минут

Блог: Oracle SQL
Чалышев Максим Михайлович
SQL. 5 дней, которые изменят твою жизнь.

Бесплатные авторские видеокурсы SQL, PLSQL, JAVA

изучаем SQL группа в контакте - присоединяйтесь
моя книга , изучаем Oracle SQL, бесплатно
Oracle SQL. 100 шагов от новичка до профессионала. 20 дней новых знаний и практики или платная печатная версия

Применяется в основном для отчетности , для следующих типов задач
1. Запросы рейтингов, первых N
2. Запросы с накопительным итогом
3. Запросы с конструкцией окна
4. Может применятся и для оптимизации запросов

Какие бывают функции в аналитическом, разберём основные
ROW_NUMBER() – номер строки в группе
LAG(f, n,m) –f имя поля, n предыдущее значение в группе, m – знач по умолчанию
LEAD(f, n,m) -f имя поля, n последующее значение в группе, m – знач по умолчанию
FIRST_VALUE(f) – f имя поля, первое значение в группе ,
LAST_VALUE(f) –f имя поля, последнее значение в группе
STD_DEV(f) – f имя поля, значение стандартного распределения в группе
SUM(f) – f имя поля, накопительная сумма по группе
AVG (f)– f имя поля, среднее по группе заданной групп
RANK(f) – f имя поля, относительный ранг записи в группе


Синтаксис
SELECT аналитическая функция OVER([PARTITION партицирование…] 
ORDER BY (упорядочивание выражение 2 [,…] [{ASC/DESC}] [{NULLS FIRST/NULLS LAST}]) а


читать дальше...
автор: Myp3_u_K добавлено: 15 апр 15 просмотры: 104376, комментарии: 0



INSERT ALL - Множественная вставка

Блог: Oracle SQL
Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA

Моя книга, электронная версия бесплатно
Oracle SQL. 100 шагов от новичка до профессионала. 20 дней новых знаний и практики или платная печатная версия

Как было бы хорошо с помощью одной команды Insert добавлять много записей сразу в разные таблицы :)
И такая возможность существует. Для этого используется команда INSERT ALL


INSERT ALL  Синтаксис
INSERT ALL INTO <table_name1> VALUES <value_list)
INTO <table_name2> VALUES <value_list>)
...<SELECT Statement>;
Где table_name1, table_name1 таблицы ,  
...<SELECT Statement> - запрос  для вставки данных

Приведем пример
Создадим две таблицы tab1,tab2
create table tab1(id number , val number);

create table tab2(id number , val number);



Используя инструкцию INSERT ALL INTO добавим данные в эти таблицы

insert into tab2 values(9000,9000);
commit;
insert all 
into tab2(id,val) values(1001,1112)
into tab1(id,val) select id,val from tab2 ;
commit;


Второй вариант вставки

insert all 
into tab2(id,val) values(1001,1112)
into tab1(id,val) select id,val from tab2 ;


Дополнительный интерес представляет конструкция insert all с условным выражением приведем пример использования

INSERT ALL WHEN id1 <> 11 THEN INTO tab2
values
  (id1, val1)
  select id as id1, val as val1 from tab1;
автор: Myp3_u_K добавлено: 09 апр 15 просмотры: 12033, комментарии: 2


предыдущие записи