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.
AVG + OVER (PARTITION BY ...)
dense_rank
Şö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 ...) 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
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
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 likeid tablename max1 table1 2020–03–252 table2 2020–04–303 table3 2020–02–284 table4 2020–03–31
Şöyle yaparız
select row_number() over() as id, * from (select ‘table1’ as tablename, max(date) from table1unionselect ‘table2’ as tablename, max(date) from table2unionselect ‘table3’ as tablename, max(date) from table3unionselect ‘table4’ as tablename, max(date) from table4order by tablename) t1;
sum
Örnek
Veri şöyle olsun
OVER için açıklama şöyle
Over turns an aggregation function into a window functionYani 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ıyorSELECT 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ı şöyleAs 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 okunurSELECT 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.
Hiç yorum yok:
Yorum Gönder