Информация

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

Теги


Блоги


Записи из всех блогов с тегом: with


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

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


-- подготовим данные для тестового примера
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 просмотры: 873, комментарии: 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 просмотры: 1271, комментарии: 1



WITH в ORACLESQL

Блог: Oracle SQL
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы 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 просмотры: 4586, комментарии: 2



Парсинг строки с разделителями - Delphi

Блог: Ramin Hashimzade
функция который возвращает кол. массива.

function DCOUNT(str, Delimeter: string) : integer;
var
 StrL : TStringList;
 ParseStr : string;
begin
  try
    StrL := TStringList.Create;
    ParseStr:= StringReplace(str, Delimeter, #13, [rfReplaceAll]);
    StrL.Text := ParseStr;
    Result := StrL.Count;
  finally
    StrL.Free;
  end;
end;

Пример :
DCOUNT('test1|test2|test3|test4', '|') = 4


function StrBreak(str, Delimeter: string; fromParts, Cnt : integer) : string;
{
 str : your string
 Delimeter : Delimeter symbol
 fromParts : Initial position
 Cnt : Number of items
}
var
 StrL : TStringList;
 ParseStr : string;
 i : integer;
begin
  Result := '';
  try
    StrL := TStringList.Create;
    ParseStr:= StringReplace(str, Delimeter, #13, [rfReplaceAll]);
    StrL.Text := ParseStr;
    if StrL.Count > 0 then
    begin
      for i := 0 to Cnt-1 do
        if (StrL.Count > i) and (StrL.Count > fromParts) then
            if i>0 then Result := Result + Delimeter + StrL.Strings[fromParts+i]
                    else Result := Result + StrL.Strings[fromParts+i];
    end;
  finally
    StrL.Free;
  end;
end;

Пример :
StrBreak('test1|test2|test3|test4', '|', 0,1) = test1
StrBreak('test1|test2|test3|test4', '|', 0,2) = test1|test2
StrBreak('test1|test2|test3|test4', '|', 1,2) = test2|test3
StrBreak('test1|test2|test3|test4', '|', 3,1) = test4