Dynamic Window/Analytic Functions

CONTENTS

Home

Sometimes a static window function is not enough. In this (dialect agnostic) example, the columns you want to sum depend on the columns selected in the outside select. Instead of partition_by like the previous example, this one uses partition_by_including.

dynamic partition_by bipp

Select the columns:

dynamic partition_by columns

bipp generates the SQL:

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

Fetch the data:

dynamic partition_by results

IN THIS PAGE