I have query problem, I’m using Google BigQuery (Just to give you context if it is different). I need two value which is Value_A and Value_B. Value_A is top X value, and Value_B is the rest of top X value.Here’s my Input Table
Date Value 20 10 19 10 18 10 17 10 16 10 15 10 14 10 13 10 12 10 11 10 10 10 9 10 8 10 7 10 6 10 5 10 4 10 3 10 2 10 1 10
In this case, the value of X is 6, but I need to be configurable.
In date 20, Value_A is sum from the 6 top data in Value (which is date 14 to 20), and Value_B is sum of rest of top data (which is date 14 an below).
In date 19, Value_A is sum from the 6 top data in Value (which is date 13 to 19), and Value_B is sum of rest of top data (which is date 13 an below).
Here’s my output
Date Value Value_A Value_B 20 10 60 140 19 10 60 130 18 10 60 120 17 10 60 110 16 10 60 100 15 10 60 90 14 10 60 80 13 10 60 70 12 10 60 60 11 10 60 50 10 10 60 40 9 10 60 30 8 10 60 20 7 10 60 10 6 10 60 0 5 10 50 0 4 10 40 0 3 10 30 0 2 10 20 0 1 10 10 0
Advertisement
Answer
Below is for BigQuery Standard SQL
#standardSQL SELECT date, value, SUM(value) OVER(ORDER BY date DESC ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING) Value_A, IFNULL(SUM(value) OVER(ORDER BY date DESC ROWS BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING), 0) Value_B FROM `project.dataset.table` -- ORDER BY date DESC
If to apply to sample data from your question, as in below example
#standardSQL WITH `project.dataset.table` AS ( SELECT 20 date, 10 value UNION ALL SELECT 19, 10 UNION ALL SELECT 18, 10 UNION ALL SELECT 17, 10 UNION ALL SELECT 16, 10 UNION ALL SELECT 15, 10 UNION ALL SELECT 14, 10 UNION ALL SELECT 13, 10 UNION ALL SELECT 12, 10 UNION ALL SELECT 11, 10 UNION ALL SELECT 10, 10 UNION ALL SELECT 9, 10 UNION ALL SELECT 8, 10 UNION ALL SELECT 7, 10 UNION ALL SELECT 6, 10 UNION ALL SELECT 5, 10 UNION ALL SELECT 4, 10 UNION ALL SELECT 3, 10 UNION ALL SELECT 2, 10 UNION ALL SELECT 1, 10 ) SELECT date, value, SUM(value) OVER(ORDER BY date DESC ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING) Value_A, IFNULL(SUM(value) OVER(ORDER BY date DESC ROWS BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING), 0) Value_B FROM `project.dataset.table` ORDER BY date DESC
result is
Row date value Value_A Value_B 1 20 10 60 140 2 19 10 60 130 3 18 10 60 120 4 17 10 60 110 5 16 10 60 100 6 15 10 60 90 7 14 10 60 80 8 13 10 60 70 9 12 10 60 60 10 11 10 60 50 11 10 10 60 40 12 9 10 60 30 13 8 10 60 20 14 7 10 60 10 15 6 10 60 0 16 5 10 50 0 17 4 10 40 0 18 3 10 30 0 19 2 10 20 0 20 1 10 10 0