search

Joldnine / joldnine.github.io

My github.io blog repo. https://joldnine.github.io
2 stars 1 forks source link

SQL: An example of FIRST_VALUE #35

Open Joldnine opened 5 years ago

Joldnine commented 5 years ago

In a page view table, get the user id of non-login users from their future login page views:

SELECT FIRST_VALUE(user_id, TRUE) -- set TRUE to ignore NULL values
    OVER (
        PARTITION BY device_id 
        ORDER BY visit_time ASC
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -- only get the future
    )
FROM page_view
WHERE stat_date='20190510' -- the table is partitioned by stat_date
;