CODEWITHSUNDEEP
google-bigquery
Gaunt
Gaunt

Reputation: 714

SELECT if exists where DATE=TODAY, if not where DATE=YESTERDAY

I have a table with some columns and a date column (that i made a partition with) For example

[Amount, Date    ]  
[4     , 2020-4-1]  
[3     , 2020-4-2]  
[5     , 2020-4-4]

I want to get the latest Amount based on the Date.

I thought about doing a LIMIT 1 with ORDER BY, but, is that optimized by BigQuery or it will scan my entire table?

I want to avoid costs at all possible, I thought about doing a query based on the date today, and if nothing found search for yesterday, but I don't know how to do it in only one query.

Upvotes: 1

Views: 4838

Answers (3)

rmesteves
rmesteves

Reputation: 4075

If your date field is a partition, you can use it in WHERE clause to filter which partitions should be read in your query.

In your case, you could do something like:

SELECT value
FROM <your-table>
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
ORDER BY Data DESC
LIMIT 1

This query basically will:

  1. Filter only today's and yesterday's partitions
  2. Order the rows by your Date field, from the most recent to the older
  3. Select the first element of the ordered list

If the table has a row with today's date, the query will return the data for today. If it dont't, the query will return the data for yesterday.

Finally, I would like to attach here this reference regarding querying partitioned tables.

I hope it helps

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172984

Below is for BigQuery Standard SQL 

#standardSQL
SELECT ARRAY_AGG(amount ORDER BY `date` DESC LIMIT 1)[SAFE_OFFSET(0)]
FROM `project.dataset.table`
WHERE `date` >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)

Note: above assumes your date field is of DATE data type.

Upvotes: 3

gnunez88
gnunez88

Reputation: 13

The LIMIT order stops the query whet it gets the amount of results indicated. I think the query should be something like this, I'm not sure if "today()-1" returns 

SELECT Amount
FROM <table> as t
WHERE date(t.Date) = current_date()
OR date(t.Date) = makedate(year(current_date()), dayofyear(current_date())-1);

Edited: Sorry, my answer is for MariaDB I now see you ask for Google-BigQuery which I didn't even know, but it looks like SQL, I hope it has some functions like the ones I posted.

Upvotes: 0

Related Questions