Oracle SQL

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


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

Чалышев Максим Михайлович
Учебный тренинг. 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])
добавлено: 23 май 18 просмотры: 2518, комментарии: 1



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

Чалышев Максим Михайлович
Учебный тренинг. 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
читать дальше...
добавлено: 17 май 18 просмотры: 3315, комментарии: 0



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

Чалышев Максим Михайлович
Учебный тренинг. 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
добавлено: 02 май 18 просмотры: 3950, комментарии: 1



WITH в ORACLESQL

Чалышев Максим Михайлович
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
добавлено: 21 ноя 17 просмотры: 14125, комментарии: 2



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

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);


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



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

Чалышев Максим Михайлович
Учебный тренинг. 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


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



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

Чалышев Максим Михайлович
Учебный тренинг. 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 ...) ;

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



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

Чалышев Максим Михайлович
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}]) а


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



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

Чалышев Максим Михайлович
Учебный тренинг. 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;
добавлено: 09 апр 15 просмотры: 10593, комментарии: 2



Множественный UPDATE

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

изучаем SQL группа в контакте - присоединяйтесь


Итак, небольшая заметка по операторы UPDATE
предположим что нам необходимо заменить значения в таблице 1 , данными из таблицы 2
можно конечно написать что то вроде
update t1 set t1.f1=select t2.f1 from t2 where t2.key1 = t1.key

но у нас есть более легкий способ произвести подобную операцию
UPDATE
(
SELECT
       t.c1,
       t.c2,
       s.c1 AS c1_new,
       s.c2 AS c2_new
 
FROM table1 t
INNER JOIN table2 s ON s.key=t.key
)
tt
SET tt.c1=tt.c1_new, tt.c2=tt.c2_new


Где table1, table2 это
То есть на основании данных из одной таблицы мы обновляем данные в другой таблице, важно наличие первичного ключа в каждой из представленных таблиц
приведем пример демонстрирующий работу UPDATE с несколькими таблицами

create table PHONES1
(
  PHONENUM VARCHAR2(48) not null primary key,
  NAME VARCHAR2(48)
);

create table PHONES
(
  PHONENUM VARCHAR2(48) not null primary key,
  NAME VARCHAR2(48)
);


заполним данными и выполним UPDATE
INSERT INTO PHONES1(PHONENUM, name) values ('495 1211133','Алексеев1');
INSERT INTO PHONES1(PHONENUM, name) values ('499 3311133','Михайлов1');
INSERT INTO PHONES1(PHONENUM, name) values ('917 12122sd3','Коробочкин1');
INSERT INTO PHONES1(PHONENUM, name) values ('499 33111133','Костин1');

INSERT INTO PHONES(PHONENUM, name) values ('495 1211133','Алексеев');
INSERT INTO PHONES(PHONENUM, name) values ('499 3311133','Михайлов');
INSERT INTO PHONES(PHONENUM, name) values ('917 12122sd3','Коробочкин');
INSERT INTO PHONES(PHONENUM, name) values ('499 33111133','Костин');
INSERT INTO PHONES(phonenum, name) values ('214 331das1133','Докучаев');

UPDATE (SELECT   t.phonenum AS phonenum1,       t.phonenum phonenum2,        s.name AS name1, t.name AS name_new FROM phones1 t
INNER JOIN phones s ON s.phonenum=t.phonenum )  Tt SET tt.name1=tt.name_new

результат - обновление данных в phones из таблицы phones1
добавлено: 08 апр 15 просмотры: 5087, комментарии: 3