Simple Log Service:Fungsi jendela

Pendahuluan

Berbeda dengan fungsi agregat yang menggabungkan baris menjadi satu hasil tunggal, fungsi jendela menghitung hasil untuk setiap baris berdasarkan sekumpulan baris terkait. Fungsi jendela memiliki tiga elemen inti: partisi, pengurutan, dan bingkai (Konsep dan Sintaksis Fungsi Jendela).

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

• Partisi: Klausul partition by membagi baris ke dalam partisi. Jika klausul ini dihilangkan, seluruh set hasil diperlakukan sebagai satu partisi.

• Pengurutan: Klausul order by mengurutkan baris dalam setiap partisi.

  Catatan

  Menggunakan klausul order by pada kolom yang memiliki nilai duplikat menghasilkan urutan baris yang tidak deterministik. Untuk memastikan urutan pengurutan yang konsisten, tentukan beberapa kolom. Misalnya, order by request_time, request_method.

• Bingkai: Membatasi baris dalam suatu partisi. Tidak dapat digunakan dengan fungsi peringkat. Sintaksis: { rows | range} { frame_start | frame_between }. Contoh: range between unbounded preceding and unbounded following. Spesifikasi Bingkai Fungsi Jendela.

Fungsi

Fungsi agregat

Semua fungsi agregat dapat berfungsi sebagai fungsi jendela. Contoh berikut menggunakan sum().

Sintaksis

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

Parameter

Tipe nilai kembali

double

Contoh

Menghitung gaji setiap karyawan sebagai proporsi dari total departemen mereka.

• Pernyataan kueri

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

• Hasil kueri: Hasil menampilkan data untuk empat karyawan di departemen dev dan tiga di departemen Marketing. Kolom salary_percentage menunjukkan gaji setiap karyawan sebagai pecahan dari total departemen mereka.

Fungsi cume_dist

Menghitung distribusi kumulatif suatu nilai dalam partisi: rasio baris dengan nilai kurang dari atau sama dengan nilai baris saat ini terhadap total baris. Mengembalikan nilai dalam rentang (0, 1].

Sintaksis

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

Parameter

Tipe nilai kembali

double

Contoh

Menghitung distribusi kumulatif ukuran objek dalam bucket OSS bucket00788.

• Pernyataan kueri

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

Fungsi dense_rank

Mengembalikan peringkat suatu nilai dalam partisi. Nilai yang sama menerima peringkat yang sama, dan peringkat berurutan tanpa celah. Misalnya, jika dua baris berbagi peringkat 1, peringkat berikutnya adalah 2.

Sintaksis

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

Parameter

Tipe nilai kembali

bigint

Contoh

Menghitung peringkat gaji berdasarkan departemen.

• Pernyataan kueri dan analisis

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

• Hasil kueri dan analisis: Di Marketing, Blan Stark dan Smith berbagi peringkat 1 (gaji 9000); Achilles berada di peringkat 2 (8000). Di dev, Rob berada di peringkat 1 (9000), Blan peringkat 2 (8500), Sansa peringkat 3 (8000).

Fungsi ntile

Membagi baris yang diurutkan dalam partisi menjadi sejumlah kelompok tertentu.

Sintaksis

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

Parameter

Tipe nilai kembali

bigint

Contoh

Membagi data untuk objek tertentu menjadi tiga kelompok.

• Pernyataan kueri

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

• Hasil kueri dan analisis: Kueri mengembalikan sembilan baris. Nilai untuk object_size, dalam urutan naik, adalah 3396, 3701, 3750, 3757, 3914, 3918, 7440, 7490, dan 7521. Nilai yang sesuai dalam kolom ntile adalah 1, 1, 1, 2, 2, 2, 3, 3, dan 3. Akibatnya, ntile(3) membagi sembilan baris menjadi tiga kelompok yang sama besar, masing-masing tiga baris.

Fungsi percent_rank

Menghitung peringkat persentase setiap baris dalam partisi. Rumus: (rank - 1) / (total_rows - 1), di mana rank adalah peringkat baris saat ini dan total_rows adalah ukuran partisi.

Sintaksis

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

Parameter

Tipe nilai kembali

double

Contoh

Menghitung peringkat persentase objek OSS berdasarkan ukuran.

• Pernyataan kueri

  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

• Hasil kueri dan analisis: Enam baris diurutkan berdasarkan object_size. Kolom ntile menunjukkan nilai peringkat persentase yang tersebar merata dari 0,0 hingga 1,0.

Fungsi rank

Mengembalikan peringkat setiap baris dalam partisi. Nilai yang sama menerima peringkat yang sama, menciptakan celah dalam urutan. Misalnya, jika dua baris berbagi peringkat 1, peringkat berikutnya adalah 3.

Sintaksis

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

Parameter

Tipe nilai kembali

bigint

Contoh

Memberi peringkat karyawan berdasarkan gaji dalam setiap departemen.

• Pernyataan kueri dan analisis

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

• Hasil kueri dan analisis: Di Marketing, dua karyawan berbagi peringkat 1 (gaji 9000). Peringkat berikutnya adalah 3, menunjukkan bagaimana nilai yang sama menciptakan celah.

Fungsi row_number

Memberikan bilangan bulat berurutan unik kepada setiap baris dalam partisi, dimulai dari 1.

Sintaksis

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

Parameter

Tipe nilai kembali

bigint.

Contoh

Memberi peringkat karyawan berdasarkan gaji dalam setiap departemen.

• Kueri

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

• Enam baris: Di Marketing, Blan Stark (9000) berada di baris 1, Smith (9000) baris 2, Achilles (8000) baris 3. Di dev, Rob (9000) berada di baris 1, Blan (8500) baris 2, Sansa (8000) baris 3.

Fungsi first_value

Mengembalikan nilai dari baris pertama dalam setiap partisi.

Sintaksis

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

Parameter

Tipe nilai kembali

Mengembalikan tipe data yang sama dengan x.

Contoh

Mengembalikan ukuran minimum setiap objek dalam bucket OSS.

• Pernyataan kueri dan analisis

  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

• Hasil kueri dan analisis: Tujuh baris dipartisi menjadi tiga kelompok berdasarkan object. Dalam setiap kelompok, first_value sama dengan object_size minimum (baris pertama dalam urutan naik).

Fungsi last_value

Mengembalikan nilai dari baris terakhir dalam partisi.

Sintaksis

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

Parameter

Tipe nilai kembali

Tipe yang sama dengan x.

Contoh

Menemukan ukuran objek maksimum dalam bucket OSS yang ditentukan.

• Pernyataan kueri dan analisis

  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

• Kueri mengembalikan tiga kolom: object, object_size, dan last_value. Untuk 245-da918c.model, tujuh baris menunjukkan object_size dari 2383 hingga 6936, dengan last_value 6936 untuk semua baris. Untuk dashboard%2F2020%2F05%2F20%2F16%2F47.csv, dua baris memiliki object_size 2435 dan 2603, dengan last_value 2603.

Fungsi lag

Mengembalikan nilai dari baris pada offset tertentu sebelum baris saat ini dalam partisi.

Sintaksis

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

Parameter

Tipe nilai kembali

Tipe yang sama dengan x.

Contoh

Menghitung UV harian dan laju pertumbuhan hari-ke-hari.

• Pernyataan kueri

  * |
  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
    )

• Hasil kueri dan analisis: Delapan catatan dari 2–9 Agustus 2021. diff_percentage pada baris pertama adalah Infinity karena lag mengembalikan nilai default 0, menyebabkan pembagian dengan nol. Baris-baris berikutnya menunjukkan rasio UV hari ini terhadap UV hari sebelumnya, seperti 2,098 dan 0,976.

Fungsi lead

Mengembalikan nilai dari baris pada offset tertentu setelah baris saat ini dalam partisi.

Sintaksis

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

Parameter

Tipe nilai kembali

Mengembalikan nilai dengan tipe data yang sama dengan x.

Contoh

Menghitung rasio UV per jam pada 26 Agustus 2021.

• Pernyataan kueri

  * |
  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
    )

• Hasil kueri dan analisis: Kueri mengembalikan delapan baris, menunjukkan pengunjung unik per jam (UV) dan rasio UV jam saat ini terhadap UV jam berikutnya (diff_percentage) dari pukul 00.00 hingga 07.00 pada 26 Agustus 2021.

Fungsi nth_value

Mengembalikan nilai dari baris ke-offset dalam partisi.

Sintaksis

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

Parameter

Tipe nilai kembali

Sama dengan tipe data x.

Contoh

Mengembalikan karyawan dengan gaji tertinggi kedua per departemen.

• Pernyataan kueri

  * |
  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

• Hasil kueri: Kolom second_highest_salary menunjukkan Blan (8500) untuk dev dan San (7000) untuk Marketing di semua baris dalam setiap partisi.