I Have a table that looks like this:

Table1:

Time_stamp bigint, << Notice this is bigint and not timestamp, so this needs to be casted
amount int, 
name_ev text, 
badge_number int,
type text

(We only want to pull information for rows where the type is in ('customer', 'client'))

And another table that looks like this:

Table2:

timestamp TIMESTAMP,
low int,
high int

(This is by the minute, so every minute has a value low, high)

and a third table that looks like this:

Table3:

name text,
badge_number text, << Notice this is text and not int, so this needs to be casted
trip_data

(There can only exist one name, and one badge_number, meaning no dupes here)

I'm attempting to pull this:

timestamp, name, badge_number, highest_Day, lowest_Day, highest_Week, lowest_Week, highest_Month, lowest_Month, trip_data, amount_day, amount_week, amount_month, badge_day, badge_week, badge_month, last_trip_amount, last_trip_time_stamp

Explentation:

Explentation:

1. timestamp: The time_stamp from Table1
2. name: the name from Table1
3. badge_number: the badge_number from Table1
4. highest_Day: a the highest value of high in Table2 Where Table2.timestamp is between Table1.time_stamp - 1 Day and Table1.time_stamp
5. lowest_Day: a the highest value of low in Table2 Where Table2.timestamp is between Table1.time_stamp - 1 Day and Table1.time_stamp
6. (The same for Week/Month but instead of Day, it's 1 Week (7 days), and 1 Month (30 Days))...
7. trip_data: the value of trip_data from Table3 where Table3.name matches Table1.name_ev and Table3.badge_number matches Table1_badge_number
8. amount_day: the sum of amount from Table1 for all objects where Table1.time_stamp is between Table1.time_stamp - 1 Day and Table1.time_stamp
9. (The same for Week/Month but instead of Day, it's 1 Week (7 days), and 1 Month (30 Days))...
10. badge_day: is the same as amount_day, but only where Table1.badge_number matches Table1.badge_number and Table1.time_stamp is between Table1.time_stamp - 1 Day and Table1.time_stamp
11. (The same for Week/Month but instead of Day, it's 1 Week (7 days), and 1 Month (30 Days))...
12. last_trip_amount: is the value of Table1.amount where Table1.name_ev and Table1.badge_number match the records name_ev and badge_number
13. last_trip_time_stamp: is the same as above, but instead of amount it's the timestamp.

Note that Table1 is not sorted, and should be sorted on timestamp when retrieving the last object (ie sort by time_stamp desc limit 1) or whatever.

The goal is to pass a badge_number and return all records (ie ~15k) in the above response.

CodePudding user response：

Use WITH clauses to split the Big query into several smaller ones, which you can name. Each sub-query will be executed only once, and performance shouldn't be a problem.