19 Haziran 2020 Cuma

PG_NAMESPACE Sistem Tablosu - Data Catalog

Giriş
Schema bilgisini saklar. Açıklaması şöyle
"One key difference between PostgreSQL and standard relational database systems is that PostgreSQL stores much more information in its catalogs: not only information about tables and columns, but also information about data types, functions, access methods, and so on," states the developer of Postgres. Users can modify these tables, which means Postgres can be extended by a user, unlike with most conventional database systems, which require hardcoded changes in the source code to modify the software.
Örnek
Şöyle yaparız
SELECT quote_ident(nspname) FROM pg_namespace WHERE nspname LIKE '%5%';

12 Haziran 2020 Cuma

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.











10 Haziran 2020 Çarşamba

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