Window/Analytic Functions

CONTENTS

Home

This (dialect agnostic) example handles a single invoice with multiple items and different prices and quantities per item. To compute an average per customer, you first want to sum within each invoice.

partition_by bipp

Select the columns:

partition_by columns

bipp generates the SQL:

SELECT t000.customer_id AS _0,
    avg(t001.transaction_amount) AS _1
FROM `online_retail`.`transactions` AS t000
JOIN (
    SELECT transactions.invoice_num AS invoice_num,
        sum(transactions.invoice_qnty *
        transactions.unit_price) AS transaction_amount
    FROM `online_retail`.`transactions` AS transactions
    GROUP BY invoice_num) AS t001
ON t000.invoice_num=t001.invoice_num
GROUP BY 1

Fetch the data:

partition_by results

IN THIS PAGE