Analytic Functions / Window Functions - Greatest N Per Group

Giriş

Analytic Functions aynı zamanda Window Functions olarak ta isimlendiriliyor sanırım.

Grubun Toplam İçindeki Yüzdesi - % of items by type
Şöyle yaparız.

select 
    i.type,
    avg( (c.color in ('Blue', 'Green'))::int ) ratio_blue_green,
    count(distinct i.item_id) no_items
from item_info i
inner join item_color c on c.item_id = i.item_id
group by i.type

::int'in açıklaması şöyle.

this evaluates the condition as 1 when succesful, else 0. Taking an average of this gives you the ratio of rows that satisify the condition

Çıktı olarak şunu alırız

type  percent_blue_green  total_items
7         30.00               6
8         34.29               7

PARTITION BY

PARTITION BY belirtilen kritere göre gruplamak için kullanılır. Daha sonra gruplara count() over, dense_rank() over ,row_number() over gibi metodlarla ilave sütunlar atanır. 

AVG + OVER (PARTITION BY ...)

AVG + OVER (PARTITION BY ...) yazısına taşıdım

count

Örnek

Elimizde şöyle bir tablo olsun. Aynı  product_id ve entry_date değerlerine sahip sütunları göstermek isteyelim

id     product_id  entry_date                   product_name stock
1      009         2020-12-11 02:05:20.09876    apple        5
2      001         2020-12-11 03:04:10.09876    orange       9
3      004         2020-12-11 10:05:20.09876    pineapple    4
4      002         2020-12-11 02:05:20.09876    berry        5
5      009         2020-12-11 02:05:20.09876    apple        2
6      004         2020-12-11 10:05:20.09876    pineapple    1
7      006         2020-12-11 10:05:20.09876    pineapple    4

Şöyle yaparız. Burada partition by ile grupluyoruz. count(1) ile grupların eleman sayısı bulunuyor. Eleman sayısı 1'den büyük olanlar süzülüyor.

select * from
(select t.*,
       count(1) over (partition by product_id, entry_Date) as cnt
  from t) t
where cnt > 1

dense_rank

Max Per Group içindir. Kısaca bu metod ile verilen seti sıralayıp her bir satıra 1'den başlayarak sayı vermek mümkün. Verilen numaralara arasında boşluk olmaz. Sıralarken eşit olan değerlere aynı sayı veriliyor.

DENSE_RANK yazısına taşıdım

row_number
Greatest N Per Group içindir. PARTITION BY ile  gruplama yapıldıktan sonra aynı gruba düşenlere 1'den başlayan ve artarak giden bir sayı verir.

Örnek
Elimizde şöyle bir tablo olsun

postgres=# select * from test;
  col1 | col2 |    col3    
 ------+------+------------
     1 | abc  | 2015-09-10
     1 | abc  | 2015-09-11
     2 | xyz  | 2015-09-12
     2 | xyz  | 2015-09-13
     3 | tcs  | 2015-01-15
     3 | tcs  | 2015-01-18

Şöyle bir çıktı isteyelim

 col1 | col2 |    col3    
------+------+------------
    2 | xyz  | 2015-09-13
    1 | abc  | 2015-09-11
    3 | tcs  | 2015-01-18

Şöyle yaparız. col1'e göre partition yapılır. col3 yani date alanına göre azalarak sıralanır ve row number'ı bir olan ilk satır seçilir. Yani grubun ilk elemanı seçilir. Daha sonra her gruptaki elemanlar sıralanır.

SELECT col1, col2, col3 FROM  (
  SELECT col1, col2, col3 ,row_number() OVER (PARTITION BY col1 ORDER BY col3 DESC) AS rn
   FROM   test
   ) sub
WHERE  rn = 1
ORDER  BY col3 DESC, col2;

Örnek

Burada PARTITION BY kullanılmıyor ancak ROW_NUMBER() kullanımı için iyi bir örnek. Açıklaması şöyle

Suppose, we have four tables (table1,table3,table4) each having a column “date”. I want to get list of max date for each table in a single query. 

The required output should be like

id  tablename max

1   table1         2020–03–25

2   table2         2020–04–30

3   table3         2020–02–28

4   table4         2020–03–31

Şöyle yaparız

select row_number() over() as id, * from (
  select ‘table1’ as tablename, max(date) from table1
  union
  select ‘table2’ as tablename, max(date) from table2 
  union
  select ‘table3’ as tablename, max(date) from table3
  union
  select ‘table4’ as tablename, max(date) from table4
  order by tablename
) t1;

sum 

Örnek

Veri şöyle olsun

OVER için açıklama şöyle

Over turns an aggregation function into a window function

Yani OVER() içinde bir şey kullanmayabiliriz. Şöyle yaparız

SELECT
    day,
    duration,
    SUM(duration) OVER() AS total_duration
FROM
    work;

Şeklen şöyle. Burada dümdüz SUM() yapılıyor. Bir özelliği yok

OVER() PARTITION BY kullanmak için şöyle yaparız. Bu durumda her gruba SUM() uygulanıyor.

SELECT
    day,
    start,
    duration,
    SUM(duration) OVER(PARTITION BY start) AS sum_duration_by_start
FROM
    work;

Grubumuz start alanı. Şeklen şöyle. 

OVER (PARTITION BY .. SORT BY ..) kullanmak için şöyle yaparız. Bu durumda her gruba RUNNING SUM() uygulanıyor

SELECT
    day,
    start,
    duration,
    SUM(duration) OVER(PARTITION BY start ORDER BY day) 
      AS sum_duration_sort_by_day
FROM
    work;

Şeklen şöyle. Start alanına göre gruplayıp, her grubu day alanına göre sıralıyoruz.

Açıklaması şöyle

As we can see, the windows resets back to size 1 at the start of each group and incrementally expand its size by one for every next row in the group.

Eğer gruplamadan RUNNING SUM() uygulamak istersek şöyle yaparız

SELECT
    day,
    duration,
    SUM(duration) OVER(ORDER BY day) AS sum_duration_order_by_day
FROM
    work;

Şeklen şöyle.

Bu durumda her gruba RUNNING SUM() uygulamak ancak window size'ı 2 yapmak için şöyle yaparız. CURRENT ROW ve bir önceki PRECEDING okunur

SELECT
    day,
    start,
    duration,
    SUM(duration) OVER(PARTITION BY start ORDER BY day ROWS BETWEEN 
      1 PRECEDING AND CURRENT ROW) AS crazy
FROM
 work;

Şeklen şöyle.

pgcrypto Module

pg_sym_encrypt
Açıklaması şöyle.

pgp_sym_encrypt uses a salt, so you do not get the same answer each time when encrypting the same value with the same password.

pg_sym_decrypt

Örnek
Tabloyu oluşturmak için şöyle yaparız.

Create table knights(age integer, nickname bytea);

Veri eklemek için şöyle yaparız.

insert into knights values(21, PGP_SYM_ENCRYPT('ShiningArmor','AES_KEY'));
insert into knights values(32, PGP_SYM_ENCRYPT('Rigid','AES_KEY'));

Şifreli veriyi çekmek için şöyle yaparız.

SELECT 
    PGP_SYM_DECRYPT(nickname::bytea, 'AES_KEY') as name,
FROM knights WHERE ( 
    LOWER(PGP_SYM_DECRYPT(nickname::bytea, 'AES_KEY')
    LIKE LOWER('%Rigid%')
);

HSTORE Extension - Key-Value Store İçindir

Giriş
Açıklaması şöyle

hstore is a data type in PostgreSQL.It helps in implementing data in the form of key-value pairs for a single value. It is useful in scenarios, such as rows with many attributes that are rarely examined, or semi-structured data. Keys and values are simply text strings.

Açıklaması şöyle.

Interestingly, the most "holistic" approach to polyglot persistence appears to be being taken by PostgreSQL. In their database, you can have a Key-Value store, JSON documents and of course, normal relational tables and one can perform SQL between and within these different storage types.

Örnek - key alana göre sorgulama

detail sütunu bir HSTORE sütunu olsun ve key alanları department, city, manager_name olsun. Şöyle yaparız

CREATE TABLE employee (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  detail HSTORE
);

INSER INTO employee (name,detail)
VALUES
 (
  'Divyanhs',
  '"department" => "Êngineering",
   "city" => "Delhi",
   "manager_name" => "Kuki"'
  ),
  ...
);

Bir key alana göre sorgulamak için şöyle yaparız

SELECT detail -> 'city' FROM employee;

Örnek

Şöyle yaparız. score sütünü HSTORE tipinden. İsim : sayı şeklinde bir JSON nesnesi yazarız

CREATE EXTENSION HSTORE;

CREATE TABLE hstore_example (score HSTORE);

INSERT INTO hstore_example VALUES('"Jason" => 100');
INSERT INTO hstore_example VALUES('"Jack" => 200');
INSERT INTO hstore_example VALUES('"Perry" => 150');

SELECT * FROM hstore_example WHERE score ? 'Jason'; //query rows

SELECT score -> 'Jason' AS score FROM hstore_example //query by key
WHERE score -> 'Jason' IS NOT NULL;

Sütun Tipleri - jsonb - Binary Formattadır, Whitespace İçermez

Giriş

Açıklaması şöyle. JSONB tipi 2014 yılında PostgreSQL 9.4 ile eklendi

In late 2014, PostgreSQL 9.4 introduced the JSONB datatype and most importantly improved the querying efficiency by adding indexing. 

The JSONB datatype stores JSON as a binary type. This introduced overhead in processing since there was a conversion involved, but it offered the ability to index the data using GIN/Full text-based indexing and included additional operators for easy querying.

Açıklaması şöyle. Binary formatta olduğu için whitespace saklamaz.

In Postgres, JSONB is a special kind of column that can store JSON in a format optimized for reads:

json Sütun Tipi ile Farkı

Açıklaması şöyle

Postgres support two forms of JSON types.

json — storing data as textual form in databases

jsonb — storing data as binary form in databases

Kısa Bir Uyarı

Her şeyi JSONB olarak saklamak iyi bir fikir gibi gelebilir. Ancak dikkatli olmak lazım çünkü daha sonra veriyi değiştirmek zor olabiliyor. Açıklaması şöyle.

PostgreSQL has json support – but you shouldn’t use it for the great majority of what you’re doing. This goes for hstore too, and the new jsonb type. These types are useful tools where they’re needed, but should not be your first choice when modelling your data in PostgreSQL, as it’ll make querying and manipulating it harder.

Constraint

Açıklaması şöyle. JSONB sütuna constraint koyulamaz.

Postgres cannot have primary and foreign key constraints on JSONB properties, but it can extract the properties into separate columns on inserts and updates, and those columns can have the constraints.

Index

JSONB sütuna GIN Index konulabilir.

Örnek
Whitespace saklamadığını görmek için şöyle yaparız.

SELECT '{"c":0,   "a":2,"a":1}'::json, '{"c":0,   "a":2,"a":1}'::jsonb;

          json          |        jsonb 
------------------------+--------------------- 
 {"c":0,   "a":2,"a":1} | {"a": 1, "c": 0} 
(1 row)

Select İşlemi
Açıklaması şöyle

The magical @> operator allows you to easily match a key-value pair or an object inside your JSON. It indeed makes easier to match things in JSON, although there are some things you should keep in mind:

- The operator @> behaves as equals comparisons if we search for an attribute
- The operator @> behaves as contains if we search for an array

Örnek

Attribute select için şöyle yaparız.

SELECT address->'city' FROM users WHERE address @> '{"zipcode": "94537"}'

Örnek

Attribute select için şöyle yaparız. Burada doc tablosundan silinen satırlar, child_table tablosuna ekleniyor.

INSERT INTO child_table SELECT doc FROM (
  DELETE FROM docs WHERE doc @> jsonb_build_object('type', 'doc_type') RETURNING doc
);

Array

Array contains için şöyle yaparız.

SELECT * FROM users WHERE address @> '{"entrances":[{"name": "backyard"}]}'

Insert İşlemi

Örnek
Şöyle yaparız. JSON '{...}' içine alınır. Key ve value değerleri çift tırnak içine alınır.

INSERT INTO users VALUES (1, 'First User', 'user1',  
'{"streetName": "Wayside Lane", "houseNumber": 3104, "zipcode": "94538"}');

Update İşlemi
JSONB_SET() metodu kullanılır. İlk parametresi sütun ismi, ikinci parametre key, üçüncü parametre yeni value değeridir.

-> operator attribute değerini text'e çevirmek için kullanılır.

Örnek
Şöyle yaparız.

UPDATE users SET address = jsonb_set(address, '{state}', '"California"')
  WHERE address->'state' = '"CA"';

JPA İle Kullanım

jsonb - JPA İle Kullanım yazısına taşıdım

Sütun Tipleri - Serial (Otomatik Sayı Üretir) - Kullanmayın

Giriş

Açıklaması şöyle. Yani SERIAL yerine IDENTITY kullanılırsa daha iyi

It is recommended to use IDENTITY instead since SERIAL has some weird behaviors.

Serial iki çeşit. Bunlar

1. SERIAL

2. BIGSERIAL

Bu sütunlara NOT NULL + PRIMARY KEY + UNIQUE gibi özellikler de atanabilir.

Diğer

Eğer serial veya bigserial yerine sequence kullanmak istersek şöyle yaparız

CREATE TABLE public.contacts
(
  contactid integer NOT NULL DEFAULT nextval('contacts_contactid_seq'::regclass),
  ...
);

1. SERIAL Sütun Tipi

Açıklaması şöyle

the keyword serial is PostgreSQL specific and it set up an auto-incrementing value and that is the typical way for the primary-key.

Açıklaması şöyle. 4 byte uzunluğundadır. Yani integer ile aynıdır.

Serial is just syntactic sugaring on top of an int column that takes its value from a sequence.

Açıklaması şöyle. Eğer transaction başarısız olsa bile serial numarası artmaya devam eder.

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.

Örnek
Şöyle yaparız.

create table testtable(
  id serial primary key,
  data integer not null
);

Şöyle yaparız.

insert into testtable ( data ) values ( 4 ), ( 5 ), ( 6 ), ( 7 );

Örnek

Şöyle yaparız

CREATE SCHEMA retail;
CREATE TABLE retail.orders_info (
  orderid SERIAL NOT NULL PRIMARY KEY,
  ...
);

Cache Parametresi
Açıklaması şöyle.

SERIAL columns are implemented using standard SQL sequences, which might generate out-of-order values when used by multiple concurrent sessions if the CACHE parameter is set to something more than 1

Daha detaylı açıklama şöyle.

Although multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered. For example, with a cache setting of 10, session A might reserve values 1..10 and return nextval=1, then session B might reserve values 11..20 and return nextval=11 before session A has generated nextval=2. Thus, with a cache setting of one it is safe to assume that nextval values are generated sequentially; with a cache setting greater than one you should only assume that the nextval values are all distinct, not that they are generated purely sequentially.

2. BIGSERIAL Sütun Tipi
Örnek

Şöyle yaparız

CREATE TABLE foo (
  id BIGSERIAL PRIMARY KEY,
  ...
);

CREATE TABLE bar (
  foo_id BIGINT UNIQUE,
  ...
);

Örnek

Şöyle yaparız.

CREATE TABLE category
(
  id bigserial NOT NULL PRIMARY KEY,
  ...
)