Simple Log Service:Window functions

Giriş

Satırları tek bir sonuca daraltan toplama işlevlerinin aksine, window function'lar ilişkili bir satır kümesine dayalı olarak her satır için bir sonuç hesaplar. Window function'ların üç temel öğesi vardır: bölüm, sıralama ve çerçeve (Window Function Concepts and Syntax).

function over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

• Bölüm: partition by yan tümcesi satırları bölümlere ayırır. Bu yan tümce atlanırsa, tüm sonuç kümesi tek bir bölüm olarak kabul edilir.

• Sıralama: order by yan tümcesi, her bölümdeki satırları sıralar.

  Not

  Yinelenen değerlere sahip sütunlarda order by yan tümcesinin kullanılması deterministik olmayan bir satır sırasına neden olur. Tutarlı bir sıralama düzeni sağlamak için birden çok sütun belirtin. Örneğin, order by request_time, request_method.

• Çerçeve: Bir bölüm içindeki satırları kısıtlar. Sıralama işlevleriyle kullanılamaz. Sözdizimi: { rows | range} { frame_start | frame_between }. Örnek: range between unbounded preceding and unbounded following. Window Function Frame Specification.

İşlevler

Toplama işlevleri

Tüm toplama işlevleri window function olarak hizmet edebilir. Aşağıdaki örnekte sum() kullanılmıştır.

Syntax

sum() over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

Parameters

Return value type

double

Examples

Her çalışanın maaşını departman toplamına oranla hesaplar.

• Sorgu deyimi

  * |
  SELECT
    department,
    staff_name,
    salary,
    round ( salary * 1.0 / sum(salary) over(partition by department), 3) AS salary_percentage 

• Sorgu sonuçları: Sonuçlar dev departmanındaki dört çalışan ve Marketing departmanındaki üç çalışan için verileri gösterir. salary_percentage sütunu, her çalışanın maaşını departman toplamının bir kesri olarak gösterir.

Cume_dist işlevi

Bir bölüm içindeki bir değerin kümülatif dağılımını hesaplar: Değeri geçerli satırın değerinden küçük veya ona eşit olan satırların toplam satırlara oranı. (0, 1] aralığında bir değer döndürür.

Syntax

cume_dist() over (
    [partition by partition_expression]
    [order by order_expression]
)

Parameters

Return value type

double

Examples

OSS paket bucket00788'deki nesne boyutlarının kümülatif dağılımını hesaplar.

• Sorgu deyimi

  bucket=bucket00788 |
  select
    object,
    object_size,
    cume_dist() over (
      partition by object
      order by
        object_size
    ) as cume_dist
  from  oss-log-store

Dense_rank işlevi

Bir bölüm içindeki bir değerin sırasını döndürür. Eşit değerler aynı sırayı alır ve sıralar arasında boşluk olmadan ardışıktır. Örneğin, iki satır 1. sırayı paylaşıyorsa sonraki sıra 2'dir.

Syntax

dense_rank() over (
    [partition by partition_expression]
    [order by order_expression]
)

Parameters

Return value type

bigint

Examples

Departmana göre maaş sıralamasını hesaplar.

• Sorgu ve analiz deyimi

  * |
  select
    department,
    staff_name,
    salary,
    dense_rank() over(
      partition by department
      order by
        salary desc
    ) as salary_rank
  order by
    department,
    salary_rank

• Sorgu ve analiz sonuçları: Marketing departmanında Blan Stark ve Smith 1. sırayı (maaş 9000) paylaşır; Achilles 2. sıradadır (8000). dev departmanında Rob 1. sıradadır (9000), Blan 2. sırada (8500), Sansa 3. sıradadır (8000).

Ntile işlevi

Bir bölümdeki sıralı satırları belirtilen sayıda gruba böler.

Syntax

ntile(n) over (
    [partition by partition_expression]
    [order by order_expression]
)

Parameters

Return value type

bigint

Examples

Belirtilen bir nesne için verileri üç gruba böler.

• Sorgu deyimi

  object=245-da918c.model |
  select
    object,
    object_size,
    ntile(3) over (
      partition by object
      order by
        object_size
    ) as ntile
  from  oss-log-store

• Sorgu ve analiz sonuçları: Sorgu dokuz satır döndürür. Artan düzende object_size değerleri 3396, 3701, 3750, 3757, 3914, 3918, 7440, 7490 ve 7521'dir. ntile sütunundaki karşılık gelen değerler 1, 1, 1, 2, 2, 2, 3, 3 ve 3'tür. Sonuç olarak, ntile(3) dokuz satırı üçerli üç eşit gruba böler.

percent_rank işlevi

Bir bölümdeki her satırın yüzde sırasını hesaplar. Formül: (rank - 1) / (total_rows - 1); burada rank geçerli satırın sırası, total_rows ise bölüm boyutudur.

Syntax

percent_rank() over (
    [partition by partition_expression]
    [order by order_expression]
)

Parameters

Return value type

double

Examples

Bir OSS nesnesinin boyuta göre yüzde sırasını hesaplar.

• Sorgu deyimi

  object=245-da918c3e2dd9dc9cb4d9283b%2F555e2441b6a4c7f094099a6dba8e7a5f.model|
  select
    object,
    object_size,
   percent_rank() over (
      partition by object
      order by
        object_size
    ) as ntile
  FROM  oss-log-store

• Sorgu ve analiz sonuçları: object_size değerine göre sıralanmış altı satır. ntile sütunu, 0,0 ile 1,0 arasında eşit dağılmış yüzde sıra değerlerini gösterir.

Rank işlevi

Her satırın bir bölüm içindeki sırasını döndürür. Eşit değerler aynı sırayı alır ve bu da dizide boşluklar oluşturur. Örneğin, iki satır 1. sırayı paylaşıyorsa sonraki sıra 3'tür.

Syntax

rank() over (
    [partition by partition_expression]
    [order by order_expression]
)

Parameters

Return value type

bigint

Examples

Çalışanları her departman içinde maaşa göre sıralar.

• Sorgu ve analiz deyimi

  * |
  select
    department,
    staff_name,
    salary,
    rank() over(
      partition by department
      order by
        salary desc
    ) as salary_rank
  order by
    department,
    salary_rank

• Sorgu ve analiz sonuçları: Marketing departmanında iki çalışan 1. sırayı (maaş 9000) paylaşır. Sonraki sıra 3'tür; bu, eşit değerlerin nasıl boşluklar oluşturduğunu gösterir.

row_number işlevi

Bir bölümdeki her satıra 1'den başlayarak benzersiz ardışık bir tamsayı atar.

Syntax

row_number() over (
    [partition by partition_expression]
    [order by order_expression]
)

Parameters

Return value type

bigint.

Examples

Çalışanları her departman içinde maaşa göre sıralar.

• Sorgu

  * |
  select
    department,
    staff_name,
    salary,
    row_number() over(
      partition by department
      order by
        salary desc
    ) as salary_rank
  order by
    department,
    salary_rank

• Altı satır: Marketing departmanında Blan Stark (9000) 1. satır, Smith (9000) 2. satır, Achilles (8000) 3. satırdır. dev departmanında Rob (9000) 1. satır, Blan (8500) 2. satır, Sansa (8000) 3. satırdır.

First_value işlevi

Her bölümdeki ilk satırın değerini döndürür.

Syntax

first_value(x) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

Parameters

Return value type

x ile aynı veri türünü döndürür.

Examples

Bir OSS paketindeki her nesnenin minimum boyutunu döndürür.

• Sorgu ve analiz deyimi

  bucket :bucket90 |
  select
    object,
    object_size,
    first_value(object_size) over (
      partition by object
      order by
        object_size 
       range between unbounded preceding and unbounded following
    ) as first_value
  from  oss-log-store

• Sorgu ve analiz sonuçları: Yedi satır object değerine göre üç gruba bölünmüştür. Her grup içinde first_value, minimum object_size değerine (artan düzendeki ilk satır) eşittir.

Last_value işlevi

Bir bölümdeki son satırdan değeri döndürür.

Syntax

last_value(x) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

Parameters

Return value type

x ile aynı tür.

Examples

Belirtilen OSS paketindeki maksimum nesne boyutunu bulur.

• Sorgu ve analiz deyimi

  bucket :bucket90 |
  select
    object,
    object_size,
    last_value(object_size) over (
      partition by object
      order by
        object_size 
       range between unbounded preceding and unbounded following
    ) as last_value
  from  oss-log-store

• Sorgu object, object_size ve last_value olmak üzere üç sütun döndürür. 245-da918c.model için yedi satırda object_size 2383'ten 6936'ya kadar değişir ve tüm satırlar için last_value 6936'dır. dashboard%2F2020%2F05%2F20%2F16%2F47.csv için iki satırda object_size 2435 ve 2603 olup last_value 2603'tür.

Lag işlevi

Bir bölümde geçerli satırdan önceki belirtilen offset konumundaki bir satırdan değeri döndürür.

Syntax

lag(x, offset, default_value) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

Parameters

Return value type

x ile aynı tür.

Examples

Günlük UV'leri ve gün bazında büyüme oranını hesaplar.

• Sorgu deyimi

  * |
  select
    day,
    UV,
    UV * 1.0 /(lag(UV, 1, 0) over()) as diff_percentage
  from  (
      select
        approx_distinct(client_ip) as UV,
        date_trunc('day', __time__) as day
      from  log
      group by
        day
      order by
        day asc
    )

• Sorgu ve analiz sonuçları: 2-9 Ağustos 2021 tarihlerinden sekiz kayıt. İlk satırın diff_percentage değeri Sonsuz'dur çünkü lag varsayılan değer 0'ı döndürür ve bu da sıfıra bölme hatasına neden olur. Sonraki satırlar, 2,098 ve 0,976 gibi mevcut günün önceki güne göre UV oranını gösterir.

Lead işlevi

Bir bölümde geçerli satırdan sonra belirtilen offset konumundaki bir satırdan değeri döndürür.

Syntax

lead(x, offset, default_value) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

Parameters

Return value type

x ile aynı veri türünde bir değer döndürür.

Examples

26 Ağustos 2021'deki saatlik UV oranını hesaplar.

• Sorgu deyimi

  * |
  select
    time,
    UV,
    UV * 1.0 /(lead(UV, 1, 0) over()) as diff_percentage
  from  (
      select
        approx_distinct(client_ip) as uv,
        date_trunc('hour', __time__) as time
      from    log
      group by
        time
      order by
        time asc
    )

• Sorgu ve analiz sonuçları: Sorgu, 26 Ağustos 2021'de 00:00 ile 07:00 saatleri arasındaki saatlik benzersiz ziyaretçi sayısını (UV) ve mevcut saatin UV'lerinin sonraki saatin UV'lerine oranını (diff_percentage) gösteren sekiz satır döndürür.

nth_value işlevi

Bir bölümdeki offset. satırdan değeri döndürür.

Syntax

nth_value(x, offset) over (
    [partition by partition_expression]
    [order by order_expression]
    [frame]
)

Parameters

Return value type

x veri türüyle aynı.

Examples

Departman başına en yüksek ikinci maaşı alan çalışanı döndürür.

• Sorgu deyimi

  * |
  select
    department,
    staff_name,
    salary,
    nth_value(staff_name, 2) over(
      partition by department
      order by
        salary desc
        range between unbounded preceding and unbounded following
    ) as second_highest_salary from log

• Sorgu sonuçları: second_highest_salary sütunu, her bölümdeki tüm satırlar genelinde dev için Blan (8500) ve Marketing için San (7000) değerlerini gösterir.