CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
user2576951
user2576951

Reputation: 198

What is the most efficient query to get Latest rows with Group By clause on Joined tables in BigQuery?

Current Design

Table: 1_notes
------------------------------------------
| id  | text           | created_at          |
------------------------------------------
| 1_1 | u1 first note  | 2018-01-01 10:00:00 |
| 1_2 | u1 second note | 2018-01-03 10:00:00 |


Table: 1_note_timeline
---------------------------------------------------------------------
| note_id  | note_created_at     | likes_count | created_at          |
---------------------------------------------------------------------
| 1_1      | 2018-01-01 10:00:00 | 10          | 2018-01-01 10:00:00 |
| 1_1      | 2018-01-01 10:00:00 | 20          | 2018-01-02 10:00:00 |
| 1_2      | 2018-01-03 10:00:00 | 10          | 2018-01-03 10:00:00 |
| 1_1      | 2018-01-01 10:00:00 | 15          | 2018-01-03 10:00:00 |


Table: 2_notes
--------------------------------------------
| id  | text           | created_at            |
--------------------------------------------
| 2_1 | u2 first note  | 2018-01-01 10:00:00 |
| 2_2 | u2 second note | 2018-01-03 10:00:00 |


Table: 2_note_timeline
---------------------------------------------------------------------
| note_id | note_created_at     | likes_count | created_at          |
---------------------------------------------------------------------
| 2_1     | 2018-01-01 10:00:00 | 10          | 2018-01-01 10:00:00 |
| 2_1     | 2018-01-01 10:00:00 | 20          | 2018-01-02 10:00:00 |
| 2_2     | 2018-01-03 10:00:00 | 10          | 2018-01-03 10:00:00 |
| 2_1     | 2018-01-01 10:00:00 | 15          | 2018-01-03 10:00:00 |

ForEach user there are 2 tables for their notes data.

Required result should have:

Final Output

Output
---------------------------------------------------------------
| note_id | note_created_at     | likes_count | text          |
---------------------------------------------------------------
| 1_1     | 2018-01-01 10:00:00 | 15          | u1 first note |
| 2_1     | 2018-01-01 10:00:00 | 15          | u2 first note |

Upvotes: 0

Views: 132

Answers (2)

Vishantha Peiris
Vishantha Peiris

Reputation: 101

Hope this will solve your issue

SELECT TOP 1 t1.note_id, t1.note_created_at, t1.likes_count, t2.[text]
  FROM 1_note_timeline t1 INNER JOIN 1_notes t2 ON t1.note_id = t2.Id
 ORDER BY t1.created_at DESC

UNION

SELECT TOP 1 t1.note_id, t1.note_created_at, t1.likes_count, t2.[text]
  FROM 2_note_timeline t1 INNER JOIN 2_notes t2 ON t1.note_id = t2.Id
 ORDER BY t1.created_at DESC

It is better to introduce indices for the tables in order to improve performance.

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Below is for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.user_notes` AS (
  SELECT * FROM `project.dataset.user1_notes` UNION ALL
  SELECT * FROM `project.dataset.user2_notes`
), `project.dataset.user_note_timeline` AS (
  SELECT * FROM `project.dataset.user1_note_timeline` UNION ALL
  SELECT * FROM `project.dataset.user2_note_timeline`
)
SELECT note_id, note_created_at, likes_count, text 
FROM (
  SELECT note_id, ARRAY_AGG(STRUCT(note_created_at, likes_count, created_at) ORDER BY created_at DESC LIMIT 1)[OFFSET(0)].*
  FROM `project.dataset.user_note_timeline`
  GROUP BY note_id
  ORDER BY likes_count DESC, note_created_at
  LIMIT 2
) t
JOIN `project.dataset.user_notes` n
ON note_id  = id

Upvotes: 1

Related Questions