What is the most efficient query to get Latest row

问题:

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.

• {{userId}}_notes table contain the notes of user with id userId
• {{userId}}_note_timeline table tracks notes likes_count data every day

Required result should have:

• First 2 notes across both users that have most likes
• Likes count must be latest (and NOT MAX_VALUE because notes likes_count can decrease with time)

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 |

回答1:

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

回答2:

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.