Simple Log Service：Fungsi jendela

Pengantar

Fungsi agregat menghitung hasil tunggal untuk sekelompok baris, sedangkan fungsi jendela menghitung hasil untuk setiap baris dalam kelompok. Fungsi jendela memiliki tiga elemen utama: partisi, urutan, dan bingkai. Untuk informasi lebih lanjut, lihat Konsep dan Sintaksis Fungsi Jendela.

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

• Partisi: Elemen partisi didefinisikan oleh klausa PARTITION BY. Klausa ini membagi baris menjadi beberapa partisi. Jika tidak ditentukan, semua baris dianggap sebagai satu partisi.

• Urutan: Elemen urutan didefinisikan oleh klausa ORDER BY. Klausa ini mengurutkan baris di seluruh partisi.

  Catatan

  Jika Anda menggunakan klausa ORDER BY untuk mengurutkan baris berdasarkan bidang dengan nilai yang sama, urutan baris tersebut bersifat non-deterministik. Untuk mendapatkan urutan yang diharapkan, tambahkan bidang lain ke klausa ORDER BY. Contoh: order by request_time, request_method.

• Bingkai: Elemen bingkai didefinisikan oleh klausa FRAME. Klausa ini menentukan subset dari setiap partisi. Bingkai digunakan untuk memperhalus baris dalam setiap partisi. Anda tidak dapat menentukan klausa FRAME untuk fungsi peringkat. Sintaksis klausa FRAME adalah: { rows | range } { frame_start | frame_between }. Contoh: range between unbounded preceding and unbounded following. Untuk informasi lebih lanjut, lihat Spesifikasi Bingkai Fungsi Jendela.

Fungsi

Fungsi agregat

Semua fungsi agregat dapat digunakan sebagai fungsi jendela. Untuk informasi lebih lanjut tentang fungsi agregat, lihat Fungsi Agregat. Contoh berikut menunjukkan cara menggunakan fungsi sum sebagai fungsi jendela.

Sintaksis

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

Parameter

Tipe Nilai Pengembalian

Tipe double.

Contoh

Hitung persentase gaji setiap karyawan di setiap departemen.

• Pernyataan Kueri

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

• Hasil Kueri dan Analisis

fungsi cume_dist

Fungsi cume_dist menghitung distribusi kumulatif setiap nilai dalam partisi. Hasil diperoleh melalui pembagian. Pembilang adalah jumlah baris dengan nilai lebih kecil atau sama dengan nilai baris tertentu (termasuk baris itu sendiri), sedangkan penyebut adalah jumlah total baris dalam partisi. Perhitungan didasarkan pada urutan baris dalam partisi. Nilai pengembalian berada dalam rentang (0,1].

Sintaksis

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

Parameter

Tipe Nilai Pengembalian

Tipe double.

Contoh

Hitung distribusi kumulatif ukuran setiap objek dalam Bucket OSS bernama 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

• Hasil Kueri dan Analisis

fungsi dense_rank

Fungsi dense_rank menghitung peringkat setiap nilai bidang tertentu dalam partisi. Jika dua nilai sama, mereka diberi peringkat yang sama. Peringkat berurutan; misalnya, jika dua nilai diberi peringkat 1, peringkat berikutnya adalah 2.

Sintaksis

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

Parameter

Tipe Nilai Pengembalian

Tipe bigint.

Contoh

Hitung peringkat gaji setiap karyawan di setiap departemen.

• Pernyataan Kueri

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

fungsi ntile

Fungsi ntile membagi baris dalam setiap partisi menjadi sejumlah grup yang ditentukan oleh parameter N.

Sintaksis

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

Parameter

Tipe Nilai Pengembalian

Tipe bigint.

Contoh

Bagi baris dalam setiap partisi menjadi tiga grup.

• 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

fungsi percent_rank

Fungsi percent_rank menghitung peringkat persentase setiap baris dalam partisi. Rumus perhitungan adalah (rank - 1) / (total_rows - 1), di mana rank adalah peringkat baris saat ini, dan total_rows adalah jumlah total baris dalam partisi.

Sintaksis

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

Parameter

Tipe Nilai Pengembalian

Tipe double.

Contoh

Hitung peringkat persentase ukuran setiap objek OSS.

• 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

fungsi rank

Fungsi rank menghitung peringkat setiap nilai bidang tertentu dalam partisi. Jika dua nilai sama, mereka diberi peringkat yang sama. Peringkat tidak berurutan; misalnya, jika dua nilai diberi peringkat 1, peringkat berikutnya adalah 3.

Sintaksis

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

Parameter

Tipe Nilai Pengembalian

Tipe bigint.

Contoh

Hitung peringkat gaji setiap karyawan di setiap departemen.

• Pernyataan Kueri

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

fungsi row_number

Fungsi row_number menghitung peringkat setiap nilai bidang tertentu dalam partisi. Setiap nilai diberi peringkat unik, dimulai dari 1.

Sintaksis

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

Parameter

Tipe Nilai Pengembalian

Tipe bigint.

Contoh

Hitung peringkat gaji setiap karyawan di setiap departemen.

• Pernyataan Kueri

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

• Hasil Kueri dan Analisis

fungsi first_value

Fungsi first_value mengembalikan nilai bidang tertentu di baris pertama setiap partisi.

Sintaksis

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

Parameter

Tipe Nilai Pengembalian

Tipe data sama dengan tipe data parameter x.

Contoh

Kembalikan ukuran minimum setiap objek dalam Bucket OSS yang ditentukan.

• Pernyataan Kueri

  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

fungsi last_value

Fungsi last_value mengembalikan nilai bidang tertentu di baris terakhir setiap partisi.

Sintaksis

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

Parameter

Tipe Nilai Pengembalian

Tipe data sama dengan tipe data parameter x.

Contoh

Kembalikan ukuran maksimum setiap objek dalam Bucket OSS yang ditentukan.

• Pernyataan Kueri

  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

• Hasil Kueri dan Analisis

fungsi lag

Fungsi lag mengembalikan nilai bidang tertentu di baris yang berada 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 Pengembalian

Tipe data sama dengan tipe data parameter x.

Contoh

Hitung jumlah pengunjung unik harian (UV) ke situs web Anda dan hitung persentase peningkatan UV dibandingkan hari sebelumnya.

• 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

fungsi lead

Fungsi lead mengembalikan nilai bidang tertentu di baris yang berada 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 Pengembalian

Tipe data sama dengan tipe data parameter x.

Contoh

Hitung jumlah pengunjung unik (UV) per jam ke situs web Anda pada 2021-08-26 dan hitung perbedaan persentase antara UV dua jam berturut-turut.

• 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

fungsi nth_value

Fungsi nth_value mengembalikan nilai bidang tertentu di baris yang berada pada offset tertentu dari awal partisi.

Sintaksis

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

Parameter

Tipe Nilai Pengembalian

Tipe data sama dengan tipe data parameter x.

Contoh

Kembalikan karyawan yang memiliki gaji tertinggi kedua di setiap 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 dan Analisis