Error: Pengukuran dengan agregasi Looker (jumlah, rata-rata, min, maks, jenis daftar) mungkin tidak merujuk pada tindakan lain

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

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

Error ini disebabkan oleh pengukuran gabungan yang merujuk pada agregasi lain atau pengukuran 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 tersarang dalam SQL - sebagian besar dialek SQL tidak dapat menggandakan agregat, atau menumpuk agregasi, sehingga upaya seperti itu memicu kesalahan.

Solusi

Ada dua solusi yang memungkinkan:

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

Menggunakan ukuran non-agregat

Tindakan non-agregat — seperti type: yesno dan type: number — adalah satu-satunya tindakan yang dapat merujuk ke tindakan atau agregasi lain. Tindakan non-agregat tidak melakukan agregasi apa pun dan dengan demikian tidak akan melakukan agregasi ganda atau tersarang. Ukuran type: number atau type: yesno bertindak sebagai placeholder sehingga ukuran atau kombinasi ukuran lain dapat dirujuk di dalamnya.

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

Contoh di bawah ini 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 masa aktif pelanggan mereka ("nilai umur pelanggan rata-rata")? Ini memerlukan dua level agregasi, yaitu penggandaan atau bertingkat, termasuk:

1. Jumlah penjualan, dikelompokkan berdasarkan pelanggan

2. Rata-rata jumlah tersebut

Untuk mencapai ini dengan LookML, Anda dapat mencoba:

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

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

Namun, hal 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 kesalahan ketika agregat ganda atau tersarang digunakan dalam kueri.

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

  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 ke dalam kolom yang dapat digabungkan — di Looker, hal ini disebut melakukan dimensi ukuran. Dengan tabel turunan, ukuran total_lifetime_value menjadi dimensi. Anda kemudian 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 dalam Jelajah, ukuran average_customer_lifetime_value dapat digunakan untuk melakukan analisis yang diinginkan di Jelajah tanpa memicu error.