Error: Ukuran dengan agregasi Looker (jumlah, rata-rata, min, maks, jenis daftar) dapat tidak merujuk pada ukuran lain

Saat mengembangkan project, Anda mungkin melihat error seperti berikut di Jelajahi atau di LookML Validator:

  Measures with Looker aggregations (sum, average, min, max, list types) may not reference other measures.

Error ini disebabkan oleh ukuran gabungan yang mereferensikan agregasi atau ukuran jenis apa pun dalam definisi LookML-nya, seperti:

    SELECT AVG((COUNT(*)) ) AS users.average_count FROM demo_db.users  AS users

Pernyataan SQL seperti ini menghasilkan agregasi ganda atau bertingkat di SQL — sebagian besar dialek SQL tidak dapat melakukan agregasi ganda, atau membuat agregasi bertingkat, sehingga upaya tersebut memicu error.

Solusi

Ada dua solusi yang memungkinkan:

1. Gunakan ukuran non-agregat untuk menjalankan pernyataan SQL non-agregat di antara ukuran.
2. Gunakan tabel turunan untuk menyusun agregasi atau agregat ganda.

Menggunakan ukuran non-agregat

Ukuran non-agregat — seperti type: yesno dan type: number — adalah satu-satunya ukuran yang dapat mereferensikan ukuran atau agregasi lainnya. Pengukuran non-agregat tidak melakukan agregasi apa pun sehingga tidak akan melakukan agregasi ganda atau bertingkat. Pengukuran type: number atau type: yesno berfungsi sebagai placeholder sehingga pengukuran atau kombinasi pengukuran lainnya dapat dirujuk di dalamnya.

Misalnya, ukuran type: number digunakan untuk melakukan penghitungan antar-ukuran dan mengambil ekspresi SQL yang valid yang menghasilkan angka atau bilangan bulat.

Contoh berikut menggunakan type: number untuk menghitung persentase semua pesanan yang dibatalkan:

measure: order_count { # Base measure #1
    type: count
    sql: ${order_id} ;;
}

measure: cancelled_orders { # Base measure #2
    type: count
    filters: [status: "Cancelled"]
}

measure: percent_cancelled_orders { # New measure
    type: number
    sql: (1.0*${cancelled_orders})/ISNULL(${order_count},0) ;;
}

Menggunakan tabel turunan untuk agregasi ganda atau bertingkat

Namun, bagaimana jika agregasi bertingkat diperlukan untuk melakukan analisis? Misalnya, bagaimana jika Anda perlu mengetahui jumlah rata-rata yang dibelanjakan pelanggan selama umur pelanggan mereka ("nilai umur pelanggan rata-rata")? Hal ini memerlukan dua tingkat agregasi — penggandaan atau penyusunan bertingkat — termasuk:

1. Jumlah penjualan, yang dikelompokkan menurut pelanggan

2. Rata-rata jumlah tersebut

Untuk mencapainya dengan LookML, Anda dapat mencoba:

measure: total_revenue {
    type: sum
    sql: ${sale_price} ;;
}

measure: avg_customer_lifetime_value {
    type: average
    sql: ${total_revenue} ;;
}

Namun, tindakan ini akan memicu error karena ukuran avg_customer_lifetime_value melakukan agregasi pada ukuran total_revenue, yang sudah merupakan agregasi. Seperti yang telah dibahas sebelumnya, sebagian besar dialek SQL akan memicu error saat agregat ganda atau bertingkat digunakan dalam kueri.

Untuk mendapatkan rata-rata jumlah total_revenue di SQL, subkueri seperti berikut diperlukan:

  SELECT AVG(s.sum) FROM (SELECT SUM(x) as sum FROM ...) AS s

Solusi yang setara di Looker adalah membuat tabel turunan untuk "meratakan" ukuran total_lifetime_value menjadi kolom yang dapat digabungkan — di Looker, hal ini disebut menyediakan dimensi ukuran. Dengan tabel turunan, ukuran total_lifetime_value menjadi dimensi. Kemudian, Anda dapat membuat ukuran type: average yang mereferensikan dimensi customer_lifetime_value:

view: customer_facts {
    derived_table: {
        sql:
        SELECT
            user_id,
            COALESCE(SUM(sale_price), 0) AS customer_lifetime_value
        FROM orders
        GROUP BY user_id;;
    }

    dimension: customer_lifetime_value {
        type: number
        sql: ${TABLE}."customer_lifetime_value" ;;
    }

    measure: average_customer_lifetime_value {
        type: average
        sql: ${customer_lifetime_value} ;;
    }
}

Setelah tabel turunan customer_facts digabungkan ke Jelajahi, ukuran average_customer_lifetime_value dapat digunakan untuk melakukan analisis yang diinginkan di Jelajahi tanpa memicu error apa pun.