Tutorial: Menggunakan fungsi agregasi

  • Artikel

Fungsi agregasi memungkinkan Anda mengelompokkan dan menggabungkan data dari beberapa baris ke dalam nilai ringkasan. Nilai ringkasan tergantung pada fungsi yang dipilih, misalnya nilai hitungan, maksimum, atau rata-rata.

Dalam tutorial ini, Anda akan mempelajari cara:

Contoh dalam tutorial ini menggunakan StormEvents tabel , yang tersedia untuk umum di kluster bantuan. Untuk menjelajahi dengan data Anda sendiri, buat kluster gratis Anda sendiri.

Tutorial ini dibangun berdasarkan fondasi dari tutorial pertama, Pelajari operator umum.

Prasyarat

  • Akun Microsoft atau Microsoft Entra identitas pengguna untuk masuk ke kluster bantuan

Gunakan operator ringkasan

Operator ringkasan sangat penting untuk melakukan agregasi atas data Anda. Operator summarize mengelompokkan baris berdasarkan by klausul lalu menggunakan fungsi agregasi yang disediakan untuk menggabungkan setiap grup dalam satu baris.

Temukan jumlah peristiwa menurut status menggunakan summarize dengan fungsi agregasi hitungan .

Menjalankan kueri

StormEvents
| summarize TotalStorms = count() by State

Output

Provinsi TotalStorms
TEXAS 4701
KANSAS 3166
IOWA 2337
ILLINOIS 2022
MISSOURI 2016
... ...

Memvisualisasikan hasil kueri

Memvisualisasikan hasil kueri dalam bagan atau grafik dapat membantu Anda mengidentifikasi pola, tren, dan outlier dalam data Anda. Anda dapat melakukan ini dengan operator render .

Sepanjang tutorial, Anda akan melihat contoh cara menggunakan render untuk menampilkan hasil Anda. Untuk saat ini, mari kita gunakan render untuk melihat hasil dari kueri sebelumnya dalam bagan batang.

Menjalankan kueri

StormEvents
| summarize TotalStorms = count() by State
| render barchart

Cuplikan layar total badai menurut bagan batang status yang dibuat dengan operator render.

Menghitung baris secara kondisional

Saat menganalisis data Anda, gunakan countif() untuk menghitung baris berdasarkan kondisi tertentu untuk memahami berapa banyak baris yang memenuhi kriteria tertentu.

Kueri berikut menggunakan countif() untuk menghitung badai yang menyebabkan kerusakan. Kueri kemudian menggunakan top operator untuk memfilter hasil dan menampilkan status dengan jumlah kerusakan pemangkasan tertinggi yang disebabkan oleh badai.

Menjalankan kueri

StormEvents
| summarize StormsWithCropDamage = countif(DamageCrops > 0) by State
| top 5 by StormsWithCropDamage

Output

Provinsi StormsWithCropDamage
IOWA 359
NEBRASKA 201
MISSISSIPPI 105
CAROLINA UTARA 82
MISSOURI 78

Mengelompokkan data ke dalam bin

Untuk mengagregasi menurut nilai numerik atau waktu, Pertama-tama Anda ingin mengelompokkan data ke dalam bin menggunakan fungsi bin(). Menggunakan bin() dapat membantu Anda memahami bagaimana nilai didistribusikan dalam rentang tertentu dan membuat perbandingan antara periode yang berbeda.

Kueri berikut menghitung jumlah badai yang menyebabkan kerusakan pemangkasan untuk setiap minggu pada tahun 2007. Argumen 7d mewakili seminggu, karena fungsi memerlukan nilai rentang waktu yang valid.

Menjalankan kueri

StormEvents
| where StartTime between (datetime(2007-01-01) .. datetime(2007-12-31)) 
    and DamageCrops > 0
| summarize EventCount = count() by bin(StartTime, 7d)

Output

Waktu mulai EventCount
01-01-2007T00:00:00Z 16
2007-01-08T00:00:00Z 20
2007-01-29T00:00:00Z 8
2007-02-05T00:00:00Z 1
2007-02-12T00:00:00Z 3
... ...

Tambahkan | render timechart ke akhir kueri untuk memvisualisasikan hasilnya.

Menjalankan kueri

Cuplikan layar kerusakan pemangkasan menurut bagan waktu minggu yang dirender oleh kueri sebelumnya.

Catatan

bin() mirip floor() dengan fungsi dalam bahasa pemrograman lainnya. Ini mengurangi setiap nilai ke kelipatan terdekat dari modulus yang Anda berikan dan memungkinkan summarize untuk menetapkan baris ke grup.

Menghitung min, maks, rata-rata, dan jumlah

Untuk mempelajari selengkapnya tentang jenis badai yang menyebabkan kerusakan tanaman, hitung kerusakan pemangkasan min(), max(), dan avg() untuk setiap jenis peristiwa, lalu urutkan hasilnya berdasarkan kerusakan rata-rata.

Perhatikan bahwa Anda dapat menggunakan beberapa fungsi agregasi dalam satu summarize operator untuk menghasilkan beberapa kolom komputasi.

Menjalankan kueri

StormEvents
| where DamageCrops > 0
| summarize
    MaxCropDamage=max(DamageCrops), 
    MinCropDamage=min(DamageCrops), 
    AvgCropDamage=avg(DamageCrops)
    by EventType
| sort by AvgCropDamage

Output

EventType MaxCropDamage MinCropDamage AvgCropDamage
Beku/Beku 568600000 3000 9106087.5954198465
Wildfire 21000000 10000 7268333.333333333
Kekeringan 700000000 2000 6763977.8761061952
Banjir 500000000 1000 4844925.23364486
Angin Badai Petir 22000000 100 920328.36538461538
... ... ... ...

Hasil kueri sebelumnya menunjukkan bahwa peristiwa Frost/Freeze menghasilkan kerusakan pemangkasan paling banyak rata-rata. Namun, kueri bin() menunjukkan bahwa peristiwa dengan kerusakan pemangkasan sebagian besar terjadi di bulan-bulan musim panas.

Gunakan sum() untuk memeriksa jumlah total tanaman yang rusak alih-alih jumlah peristiwa yang menyebabkan beberapa kerusakan, seperti yang dilakukan pada count()kueri bin() sebelumnya.

Menjalankan kueri

StormEvents
| where StartTime between (datetime(2007-01-01) .. datetime(2007-12-31)) 
    and DamageCrops > 0
| summarize CropDamage = sum(DamageCrops) by bin(StartTime, 7d)
| render timechart

Cuplikan layar bagan waktu memperlihatkan kerusakan pemangkasan menurut minggu.

Sekarang Anda dapat melihat puncak kerusakan tanaman pada bulan Januari, yang mungkin disebabkan oleh Frost /Freeze.

Tip

Gunakan minif(), maxif(), avgif(), dan sumif() untuk melakukan agregasi bersyarat, seperti yang kami lakukan ketika berada di bagian jumlah baris secara kondisional .

Menghitung persentase

Menghitung persentase dapat membantu Anda memahami distribusi dan proporsi nilai yang berbeda dalam data Anda. Bagian ini mencakup dua metode umum untuk menghitung persentase dengan Bahasa Kueri Kusto (KQL).

Menghitung persentase berdasarkan dua kolom

Gunakan count() dan countif untuk menemukan persentase peristiwa badai yang menyebabkan kerusakan tanaman di setiap status. Pertama, hitung jumlah total badai di setiap negara bagian. Kemudian, hitung jumlah badai yang menyebabkan kerusakan tanaman di setiap status.

Kemudian, gunakan perluas untuk menghitung persentase antara dua kolom dengan membagi jumlah badai dengan kerusakan tanaman dengan jumlah total badai dan dikalikan dengan 100.

Untuk memastikan bahwa Anda mendapatkan hasil desimal, gunakan fungsi todouble() untuk mengonversi setidaknya salah satu nilai jumlah bilangan bulat menjadi ganda sebelum melakukan pembagian.

Menjalankan kueri

StormEvents
| summarize 
    TotalStormsInState = count(),
    StormsWithCropDamage = countif(DamageCrops > 0)
    by State
| extend PercentWithCropDamage = 
    round((todouble(StormsWithCropDamage) / TotalStormsInState * 100), 2)
| sort by StormsWithCropDamage

Output

Provinsi TotalStormsInState StormsWithCropDamage PercentWithCropDamage
IOWA 2337 359 15.36
NEBRASKA 1766 201 11.38
MISSISSIPPI 1218 105 8.62
CAROLINA UTARA 1721 82 4.76
MISSOURI 2016 78 3.87
... ... ... ...

Catatan

Saat menghitung persentase, konversikan setidaknya salah satu nilai bilangan bulat dalam pembagian dengan todouble() atau toreal(). Ini akan memastikan bahwa Anda tidak mendapatkan hasil yang terpotok karena pembagian bilangan bulat. Untuk informasi selengkapnya, lihat Jenis aturan untuk operasi aritmatika.

Menghitung persentase berdasarkan ukuran tabel

Untuk membandingkan jumlah badai berdasarkan jenis peristiwa dengan jumlah total badai dalam database, pertama-tama simpan jumlah total badai dalam database sebagai variabel. Pernyataan let digunakan untuk menentukan variabel dalam kueri.

Karena pernyataan ekspresi tabular mengembalikan hasil tabular, gunakan fungsi toscalar() untuk mengonversi hasil count() tabular fungsi menjadi nilai skalar. Kemudian, nilai numerik dapat digunakan dalam perhitungan persentase.

Menjalankan kueri

let TotalStorms = toscalar(StormEvents | summarize count());
StormEvents
| summarize EventCount = count() by EventType
| project EventType, EventCount, Percentage = todouble(EventCount) / TotalStorms * 100.0

Output

EventType EventCount Persentase
Angin Badai Petir 13015 22.034673077574237
Hujan es 12711 21.519994582331627
Banjir Bandang 3688 6.2438627975485055
Kekeringan 3616 6.1219652592015716
Cuaca Musim Dingin 3349 5.669928554498358
... ... ...

Ekstrak nilai unik

Gunakan make_set() untuk mengubah pilihan baris dalam tabel menjadi array nilai unik.

Kueri berikut menggunakan make_set() untuk membuat array jenis peristiwa yang menyebabkan kematian di setiap status. Tabel yang dihasilkan kemudian diurutkan berdasarkan jumlah jenis storm di setiap array.

Menjalankan kueri

StormEvents
| where DeathsDirect > 0 or DeathsIndirect > 0
| summarize StormTypesWithDeaths = make_set(EventType) by State
| project State, StormTypesWithDeaths
| sort by array_length(StormTypesWithDeaths)

Output

Provinsi StormTypesWithDeaths
CALIFORNIA ["Badai Petir Angin","Selancar Tinggi","Dingin/Angin Dingin","Angin Kencang","Rip Current","Panas","Panas Berlebihan","Wildfire","Badai Debu","Pasang Rendah Astronomis","Kabut Padat","Cuaca Musim Dingin"]
TEXAS ["Flash Flood","Thunderstorm Wind","Tornado","Lightning","Flood","Ice Storm","Winter Weather","Rip Current","Excessive Heat","Dense Fog","Badai (Topan)","Dingin/Angin Dingin"]
OKLAHOMA ["Banjir Bandang","Tornado","Dingin/Angin Dingin","Badai Musim Dingin","Salju Lebat","Panas Berlebihan","Panas","Badai Es","Cuaca Musim Dingin","Kabut Padat"]
NEW YORK ["Flood","Lightning","Thunderstorm Wind","Flash Flood","Winter Weather","Ice Storm","Extreme Cold/Wind Chill","Winter Storm","Heavy Snow"]
KANSAS ["Badai Petir Angin","Hujan Lebat","Tornado","Banjir","Banjir Bandang","Petir","Salju Lebat","Cuaca Musim Dingin","Badai Salju"]
... ...

Data bucket menurut kondisi

Fungsi case() mengelompokkan data ke dalam wadah berdasarkan kondisi tertentu. Fungsi mengembalikan ekspresi hasil yang sesuai untuk predikat pertama yang terpenuhi, atau ekspresi akhir lainnya jika tidak ada predikat yang terpenuhi.

Contoh grup ini menyatakan berdasarkan jumlah cedera terkait badai yang dialihkan warga negara mereka.

Menjalankan kueri

StormEvents
| summarize InjuriesCount = sum(InjuriesDirect) by State
| extend InjuriesBucket = case (
                              InjuriesCount > 50,
                              "Large",
                              InjuriesCount > 10,
                              "Medium",
                              InjuriesCount > 0,
                              "Small",
                              "No injuries"
                          )
| sort by State asc

Output

Provinsi InjuriesCount CederaBuket
ALABAMA 494 Besar
ALASKA 0 Tidak ada cedera
SAMOA AMERIKA 0 Tidak ada cedera
ARIZONA 6 Kecil
ARKANSAS 54 Besar
ATLANTIK UTARA 15 Medium
... ... ...

Buat bagan pai untuk memvisualisasikan proporsi status yang mengalami badai yang mengakibatkan sejumlah besar, sedang, atau kecil cedera.

Menjalankan kueri

StormEvents
| summarize InjuriesCount = sum(InjuriesDirect) by State
| extend InjuriesBucket = case (
                              InjuriesCount > 50,
                              "Large",
                              InjuriesCount > 10,
                              "Medium",
                              InjuriesCount > 0,
                              "Small",
                              "No injuries"
                          )
| summarize InjuryBucketByState=count() by InjuriesBucket
| render piechart

Cuplikan layar bagan pai antarmuka pengguna web Azure Data Explorer yang dirender oleh kueri sebelumnya.

Melakukan agregasi melalui jendela geser

Contoh berikut menunjukkan cara meringkas kolom menggunakan jendela geser.

Kueri menghitung kerusakan properti minimum, maksimum, dan rata-rata tornado, banjir, dan api liar menggunakan jendela geser tujuh hari. Setiap rekaman dalam hasil yang ditetapkan mengagregasi tujuh hari sebelumnya, dan hasilnya berisi rekaman per hari dalam periode analisis.

Berikut penjelasan langkah demi langkah tentang kueri:

  1. Bin setiap rekaman ke satu hari relatif terhadap windowStart.
  2. Tambahkan tujuh hari ke nilai bin untuk mengatur akhir rentang untuk setiap rekaman. Jika nilai berada di luar rentang windowStart dan windowEnd, sesuaikan nilai yang sesuai.
  3. Buat array tujuh hari untuk setiap rekaman, mulai dari hari rekaman saat ini.
  4. Perluas array dari langkah 3 dengan mv-expand untuk menduplikasi setiap rekaman menjadi tujuh rekaman dengan interval satu hari di antaranya.
  5. Lakukan agregasi untuk setiap hari. Karena langkah 4, langkah ini benar-benar meringkas tujuh hari sebelumnya.
  6. Kecualikan tujuh hari pertama dari hasil akhir karena tidak ada periode lookback tujuh hari untuk mereka.

Menjalankan kueri

let windowStart = datetime(2007-07-01);
let windowEnd = windowStart + 13d;
StormEvents
| where EventType in ("Tornado", "Flood", "Wildfire") 
| extend bin = bin_at(startofday(StartTime), 1d, windowStart) // 1
| extend endRange = iff(bin + 7d > windowEnd, windowEnd, 
                      iff(bin + 7d - 1d < windowStart, windowStart, 
                        iff(bin + 7d - 1d < bin, bin, bin + 7d - 1d))) // 2
| extend range = range(bin, endRange, 1d) // 3
| mv-expand range to typeof(datetime) // 4
| summarize min(DamageProperty), max(DamageProperty), round(avg(DamageProperty)) by Timestamp=bin_at(range, 1d, windowStart), EventType // 5
| where Timestamp >= windowStart + 7d; // 6

Output

Tabel hasil berikut dipotong. Untuk melihat output lengkap, jalankan kueri.

Tanda waktu EventType min_DamageProperty max_DamageProperty avg_DamageProperty
2007-07-08T00:00:00Z Tornado 0 30000 6905
2007-07-08T00:00:00Z Banjir 0 200000 9261
2007-07-08T00:00:00Z Wildfire 0 200000 14033
2007-07-09T00:00:00Z Tornado 0 100000 14783
2007-07-09T00:00:00Z Banjir 0 200000 12529
2007-07-09T00:00:00Z Wildfire 0 200000 14033
2007-07-10T00:00:00Z Tornado 0 100000 31400
2007-07-10T00:00:00Z Banjir 0 200000 12263
2007-07-10T00:00:00Z Wildfire 0 200000 11694
... ... ...

Langkah selanjutnya

Sekarang setelah Anda terbiasa dengan operator kueri umum dan fungsi agregasi, lanjutkan ke tutorial berikutnya untuk mempelajari cara menggabungkan data dari beberapa tabel.

Menggabungkan data dari beberapa tabel