CODEWITHSUNDEEP
sqlgoogle-bigquery
izzatfi
izzatfi

Reputation: 25

Long Run Query to Perform Metric from Previous and Next Date in Big Query

I have a table which contain date and a metric. I want to be able to add the metric with value of the next month and also metric with the value of last year from the next month date. I have got no problem with my query, however, it took long time to run. Even after 1 hour, it still haven't finish running.

Here's my example of my table.

And here's my query in Big Query

WITH first_table AS (
  SELECT current_date, next1month_date, DATE(DATETIME_SUB(next1month_date, INTERVAL 1 YEAR)) AS previous_date, metric
  FROM (SELECT current_date, DATE(DATETIME_ADD(current_date, INTERVAL 1 MONTH)) AS next1month_date, metric
    FROM `table.name`)
),

second_table AS (
  SELECT current_date, future_metric
  FROM (SELECT current_date, metric AS future_metric
    FROM `table.name`)
),

third_table AS (
  SELECT a.current_date, a.next1month_date, a.previous_date, a.metric, b.future_metric
  FROM first_table AS a FULL JOIN second_table AS b 
  ON a.next1month_date = b.new_date
),

forth_table AS (
  SELECT current_date, previous_metric
  FROM (SELECT current_date, actual AS previous_metric
    FROM `table.name`)
)

SELECT c.current_date, c.next1month_date, c.previous_date, c.metric, c.future_metric, d.previous_metric
FROM third_table AS c FULL JOIN forth_table AS d
ON c.next1month_date = d.metric_date

And the result should be something like this:

current_date metric next1month_date future_metric previous_date previous_metric
30 Jun 2024 235345 31 Jul 2024 null 31 Jul 2023 78
31 Aug 2022 46457 30 Sep 2022 4564 30 Sep 2021 null
31 Jan 2024 6867 29 Feb 2024 46356 28 Feb 2023 345

The problem here is why it take quite long for the query to be execute? Is there any problem with my query? Is there's any, what's the wrong in my query and how should I fix it?

Upvotes: 0

Views: 37

Answers (1)

Samuel
Samuel

Reputation: 3538

The query makes use of two FULL JOIN, which require some time. My guess is that the table has several entries per date. Thus please summarize that table by date.

WITH table_one as (
SELECT DATE(current_date) AS current_date, SUM(metric) AS metric
FROM `table.name`
GROUP by 1
)


SELECT A.*,
B.metric AS previous_metric,
C.metric AS future_metric,
B.current_date AS previous_date,
C.current_date AS future_date,
FROM table_one A
FULL JOIN table_one B ON A.current_date=DATETIME_ADD(B.current_date, INTERVAL 1 MONTH)
FULL JOIN table_one C ON A.current_date=DATETIME_SUB(C.current_date, INTERVAL 1 MONTH)

Upvotes: 0

Related Questions