LEAD ve LAG

Giriş

Basic Window Functions şöyle

Aggregate Functions : SUM, COUNT, MIN, MAX, AVG

Ranking Functions : RANK, DENSE_RANK, ROW_NUMBER, NTILE

Value Functions : LAG, LEAD, FIRST_VALUE, LAST_VALUE

Açıklaması şöyle. Bir önceki veya sonraki satırın değerine erişebilme imkanı sağlar. Eğer böyle bir satır yoksa NULL döner.

The LEAD() function returns the next value of the current value.

The LAG() function returns the previous value of the current value.

Açıklaması şöyle

NOTE: LEAD() / LAG() function never works without ORDER BY clause.

Örnek

Şöyle yaparız

SELECT name, city, LEAD(amount) OVER(PARTITION BY city ORDER BY amount ASC)
AS next_amount_value FROM Orders

SELECT name, city, LAG(amount) OVER(PARTITION BY city ORDER BY amount ASC)
AS next_amount_value FROM Orders

DENSE_RANK + OVER - Gruplar ve Her Gruba Sırayla Numara Verir

Giriş

DENSE_RANK() : Satırlar gruplanır. Bir gruptaki satırlara aynı sayıyı verir. Grup değişince bir sonraki sayıdan devam eder. Verilen sayılar arasında boşluk olmaz. 

RANK(): Satırlar gruplanır. Bir gruptaki satırlara aynı sayıyı verir. Grup değişince bir sonraki sayıdan devam etmez .

ORDER_BY(): Satırları gruplamaz. Sadece sayı verir.

Şeklen şöyle

Bir başka şekil şöyle

İki kullanım var

1. DENSE_RANK + OVER (PARTITION BY ...) : 

2. DENSE_RANK + OVER (ORDER BY ...)

1. DENSE_RANK + OVER (PARTITION BY ...)

PARTITION BY + ORDER BY grubun ilk n sayıdaki elemanını çekmek için kullanılır.

Örnek

Şöyle yaparız

SELECT * FROM (
  SELECT ad,tarih ,DENSE_RANK() OVER(PARTITION BY ad ORDER BY tarih DESC) sira FROM
  kullanici)
WHERE sira=1

Örnek
Elimizde şöyle bir tablo olsun

qid  segment task    before  after   diff
------------------------------------------
1       0    aaa       5     5       0
1       0    aaa       5     5       0
1       1    aaa       15    15      0
1       1    aaa       10    5      10
1       0    aaa       10    5       5
3       0    bbb       10    4       6

Bu tablodaki aynı task grubu için en büyük qid değerini ve ona ait segment değerini çekmek isteyelim. Yani çıktı şöyle olsun

task    before  after   diff
-----------------------------
aaa       25    20      10
bbb       10    4       6

Şöyle yaparız. task alanına göre gruplayıp, qid ve segment büyükten küçüğe sıralanır. Bu satıra rnk olarak 1 verilir. Daha sonra rnk = 1 olan satırlar seçilir.

with cte as
(
    select
        *,
        dense_rank() over (partition by task order by qid desc, segment desc) as rnk
    from myTable
)

select
    task,
    sum(before) as before,
    sum(after)  as after,
    sum(diff) as  diff
from cte
where rnk = 1
group by
    task

2. DENSE_RANK + OVER (ORDER BY ...)

DENSE_RANK + OVER (ORDER BY ...) yazısına taşıdım

PostGIS ST_Contains

Örnek

Şöyle yaparız. burn_area.geom alanı içine düşen şehirleri buluruz.

SELECT city.name FROM burn_area, city
  WHERE ST_Contains(burn_area.geom, city.geom)

CREATE VIEW

Örnek - WHERE Koşulu

Şöyle yaparız

-- Create a view that pre-aggregates the spending data for each user over the last year
CREATE VIEW user_spending_last_year AS
  SELECT user_id, SUM(amount) AS total_spending
  FROM transactions
  WHERE transaction_date >= '2022-01-01'
  GROUP BY user_id;

Örnek - İki Tabloyu Birleştirmek

Elimizde iki tablo olsun

CREATE TABLE staff(
   id INT,
   age INT,
   address TEXT
);
 
 
CREATE TABLE salary(
   sal_id INT,
   salary INT,
   division TEXT
); 

Şöyle yaparız

CREATE VIEW STAFF_SALARY_COMBINED AS (
	SELECT * 
	FROM staff,salary 
	WHERE id=sal_id
);

Non-Atomic Columns

Giriş

Sütunun dizi şeklinde olmasına izin verir

Örnek

Şöyle yaparız

CREATE TABLE customers (
  name TEXT,
  address TEXT,
  payment_schedule  INTEGER[]
);

Biraz veri ekleyelim

INSERT INTO customer VALUES( 'jack','Athens, Colarado','{1,2,3,4}')
 
INSERT INTO customer VALUES( 'jackson','Tennessey, greece','{1,7,3,4}')

Dizi değerlerine erişmek için şöyle yaparız

SELECT * FROM customer WHERE
  customer.payment_schedule[1] <> customer.payment_schedule[2];

Table Inheritance

Örnek

Şöyle yaparız

CREATE TABLE customers (
  name TEXT,
  age INT,
  address TEXT
);
 
CREATE TABLE office_customers(
  office_address TEXT
) INHERITS (customer)

Biraz veri ekleyelim

INSERT INTO customers VALUES('ravi','32','32, head street');
INSERT INTO customers VALUES('michael','35','56, gotham street');

INSERT INTO office_customers VALUES('bane','28','56, circadia street','92 homebush');

ve select yapalım. Ata tabloya erişmek için ONLY kullanılır.

SELECT * FROM ONLY customer WHERE age > 20 ;

Çıktı olarak ravi ve michael satırlarını alırız. Tüm tablo hiyerarşisine erişmek için sadece SELECT yaparız

SELECT * FROM customer WHERE age > 20 ;

Çıktı olarak ravii michael ve bane satırlarını alırız.

UNIQUE Constraint

Giriş

Unique kısıtı create table esnasında veya daha sonra alter table ile eklenebilir.

Örnek

Şöyle yaparız

ALTER TABLE table ADD CONSTRAINT "abc123" UNIQUE ("col1", "col2");

Unique Sütun Null Değer Almamalı

Açıklaması şöyle. Çünkü Null unknown yani bilinmeyen değeri temsil eder. İki tane unknown ise birbiriyle karşılaştırılamaz.

In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are never considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.

Örnek

Elimizde şöyle bir constraint olsun. Yani tüm sütunları kısıta dahil edelim.

ALTER TABLE table ADD CONSTRAINT "abc123" UNIQUE
("col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8");

Aynı değere sahip iki tane satırı ekleyebildiğimizi görürüz

INSERT INTO table ("col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8") 
VALUES ('a', 'b', 'c', 'd', 'e', 'f', null, true);
INSERT INTO table ("col1", "col2", "col3", "col4", "col5", "col6", "col7", "col8") 
VALUES ('a', 'b', 'c', 'd', 'e', 'f', null, true);