Sütun Tipleri - uuid

Giriş

Açıklaması şöyle

PostgreSQL allows you to store and compare UUID values, but it doesn't have any built-in methods for creating them.

UUID üretmek için şu modüller kullanılabilir.

1. uuid-ossp Module'deki uuid_generate_v1(),uuid_generate_v4() kullanılabilir

2. pgcrypto Module'deki gen_random_uuid() kullanılabilir

3. UUIDv7 üretmek için pg_uuidv7 extension kullanılır

VARCHAR Sütun Tipi

UUID saklamak için bazen VARCHAR sütun tipi kullanılıyor.

Örnek

Şöyle yaparız. Burada id alanı UUID ama VARCHAR olarak saklanıyor

CREATE TABLE test.speed_uuid
(
    id       varchar(36) PRIMARY KEY,
    name    varchar(50),
    created timestamp
);

1. uuid-ossp Module

Açıklaması şöyle

If using Azure Database for PostgreSQL, this can be enabled by going to Server Parameters → azure.extensions → Choose uuid-ossp. User needs to have admin credentials to do this.

Örnek

Eğer kurulu değilse kurmak için şöyle yaparız

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Örnek

Şöyle yaparız

CREATE TABLE books (
  id              UUID DEFAULT uuid_generate_v4 (),
  title           VARCHAR(100) NOT NULL,
  primary_author  VARCHAR(100) NULL,
  PRIMARY KEY (id)
);

Örnek

Şöyle yaparız

CREATE EXTENSION "uuid-ossp";

INSERT INTO tenant (id, name) VALUES (uuid_generate_v4(), 'Company 1');
INSERT INTO tenant (id, name) VALUES (uuid_generate_v4(), 'Company 2');

2. pgcrypto Module

Örnek

Şöyle yaparız

CREATE TABLE thingie (
  id UUID PRIMARY KEY DEFAULT public.gen_random.uuid(),
  foo VARCHAR,
  bar VARCHAR,
);  

3. UUID7

Açıklaması şöyle

Similar to UUID v4, UUID v7 is a 128-bit identifier represented as a 32-character sequence of letters and numbers, formatted as 8–4–4–4–12. The distinctive feature of UUID v7 lies in its nature as a time-ordered UUID, encoding a Unix timestamp with millisecond precision in the most significant 48 bits. In alignment with UUID formats, 4 bits designate the UUID version, and 2 bits denote the variant. The remaining 74 bits are generated randomly, contributing to the uniqueness of this identifier.

Şeklen şöyle

Örnek

Şöyle yaparız

CREATE EXTENSION IF NOT EXISTS pg_uuidv7;
CREATE TABLE examples (
  example_id UUID PRIMARY KEY DEFAULT uuid_generate_v7(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
  deleted_at TIMESTAMP WITH TIME ZONE,
  created_by UUID NOT NULL,
  updated_by UUID NOT NULL,
  FOREIGN KEY (created_by) REFERENCES users(user_id),
  FOREIGN KEY (updated_by) REFERENCES users(user_id)
);

Analytic Functions / Window Functions

Window Function Nedir?

Açıklaması şöyle

Similar to an aggregate function, a window function operates on a set of rows. However, it does not reduce the number of rows returned by the query.

The term window describes the set of rows on which the window function operates. A window function returns values from the rows in a window.

Söz dizimi şöyle. Yani aslında bir şeyleri grupluyor ve gruptaki her satır için bir şey hesaplıyor

window_function(arg1, arg2,..) OVER (
   [PARTITION BY partition_expression]
   [ORDER BY sort_expression [ASC | DESC] [NULLS {FIRST | LAST }])

Eğer PARTITION BY kullanılmazsa tek grup varmış gibi davranır. Gruptaki satırlar ORDER BY ile sıralanabilir.

Window Function Nedir vs  SubSelect/Subquery 

Açıklaması şöyle

In a standards-compliant RDBMS, you can eliminate some of your subqueries and your in clause by using a windowing expression:

Örnek

Elimizde şöyle bir kod olsun. Harcama toplamı belli bir miktarı geçen müşterileri bulmak isteyelim. Burada GROUP BY + HAVING ile harcama tablosundaki müşteriler seçiliyor. Daha sonra JOIN ile müşteri tablosu ile birleştiriliyor.

SELECT exp.employee_id, emp.name, exp.amount
FROM Employee emp
JOIN Expenditure exp ON exp.employee_id = emp.id
WHERE exp.employee_id IN (
    SELECT employee_id
    FROM Expenditure
    GROUP BY employee_id
    HAVING SUM(amount) >= 100::money
)
ORDER BY exp.amount DESC;

Window Function' ile şöyle yaparız

SELECT exp.employee_id, emp.name, exp.amount
FROM Employee emp
JOIN (
    SELECT
        employee_id, amount,
        SUM(amount) OVER (PARTITION BY employee_id) AS total
    FROM Expenditure
) exp ON exp.employee_id = emp.id
WHERE exp.total >= 100::money
ORDER BY exp.amount DESC;

frame_clause Nedir?

Sorguda birden fazla Window Function kullanabilmeyi sağlar.

Örnek

Şöyle yaparız

SELECT
    wf1() OVER(PARTITION BY c1 ORDER BY c2),
    wf2() OVER(PARTITION BY c1 ORDER BY c2)
FROM table_name;

Eğer sürekli PARTITION BY yazmak istemiyorsak bunu kısaltmak için şöyle yaparız. Burada WINDOW kullanılıyor

SELECT 
   wf1() OVER w,
   wf2() OVER w,
FROM table_name
WINDOW w AS (PARTITION BY c1 ORDER BY c2);

Window Function Listesi

Bir açıklamaya göre Window Function Listesi şöyle. Bence bu listeye AVG ve diğer bazı şeyler de eklenmeli

Name         Description

CUME_DIST         Return the relative rank of the current row.

DENSE_RANK Rank the current row within its partition without gaps.

FIRST_VALUE Return a value evaluated against the first row within its partition.

LAG         Return a value evaluated at the row that is at a specified physical offset                                            row  before the current row within the partition.

LAST_VALUE         Return a value evaluated against the last row within its partition.

LEAD         Return a value evaluated at the row that is offset rows after the current row                                        within the partition.

NTILE         Divide rows in a partition as equally as possible and assign each row an integer                                 starting from 1 to the argument value.

NTH_VALUE         Return a value evaluated against the nth row in an ordered partition.

PERCENT_RANK Return the relative rank of the current row (rank-1) / (total rows – 1)

RANK         Rank the current row within its partition with gaps.

ROW_NUMBER Number the current row within its partition starting from 1.

Başka bir açıklamaya göre Basic Window Functions şöyle. Neyse kimin ne dediği önemli değil

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

Ranking Functions : RANK, DENSE_RANK, ROW_NUMBER, NTILE

Value Functions : LAG, LEAD, FIRST_VALUE, LAST_VALUE

FIRST_VALUE

Elimizde şöyle bir tablo olsun. Her sütundaki son null olmayan değeri isteyelim. Bu tablodaki sonuç (A1,B2,C1) olmalı

version col_A   col_B   col_C
1       A1      B1     (null)
2       A2      B3     (null)
3       A3      B2     (null)
4       A5     (null)   C1
5       A1     (null)  (null)

Şöyle yaparız. Burada frame_clause kullanılıyor. Sorguyu kısaltmak için WINDOW da kullanılabilirdi. ORDER BY ile NULL değer 0 ile değiştiriliyor ve azalan şekilde sıralanıyor. Böylece FIRST_VALUE en son sütun değerini buluyor

SELECT DISTINCT
FIRST_VALUE(col_A) OVER (ORDER BY CASE WHEN col_A IS NULL THEN 0 ELSE version END DESC)
  AS LastA,
FIRST_VALUE(col_B) OVER (ORDER BY CASE WHEN col_B IS NULL THEN 0 ELSE version END DESC)
  AS LastB,
FIRST_VALUE(col_C) OVER (ORDER BY CASE WHEN col_C IS NULL THEN 0 ELSE version END DESC)
  AS LastC
FROM tab

LAST_VALUE

Açıklaması şöyle

Q : Write a SQL query to find the total number of people who are inside the hospital.

A : the last_value window function is used to find the last value of a particular partition.

So here we can use this to find the last action of each user.

And if the last action is 'in', then the user is inside the hospital.

Şöyle yaparız

SELECT cte AS (
  SELECT*,LAST_VALUE(action) OVER(PARTITION BY emp_id) = 'in' AS 'in_action'
  FROM hospital
) 
SELECT DISTINCT emp_id FROM cte WHERE
  in_action = 1