Início / dba / Perguntas / 238197
Accepted
Brown Bear
Asked: 2019-05-15 23:31:51 +0800 CST

primeiro e último registro em uma consulta

  • 772

eu quero selecionar a primeira e a última pontuação desses dados

WITH t AS (
SELECT 1 user_id, '2019-05-15'::date AS created_at, 4 as score
UNION ALL
SELECT 1 user_id, '2019-05-13'::date AS created_at, 12 as score
UNION ALL
SELECT 2 user_id, '2019-05-15'::date AS created_at, 7 as score
UNION ALL
SELECT 3 user_id, '2019-05-13'::date AS created_at, 6 as score
),
first_score AS (
    SELECT DISTINCT ON (user_id)
        created_at, score, user_id
    FROM t
    ORDER BY user_id, created_at ASC
),
last_score AS (
    SELECT DISTINCT ON (user_id)
        created_at, score, user_id
    FROM t
    ORDER BY user_id, created_at DESC
)

select
    md5(u.user_id::varchar) user_id,
    u.created_at signup_date,
    fhs.created_at first_score_created_at,
    fhs.score first_score,
    lhs.created_at last_score_created_at,
    lhs.score last_score
from t as u 
    INNER JOIN first_score fhs ON fhs.user_id = u.user_id
    INNER JOIN last_score lhs ON lhs.user_id = u.user_id;

minha pergunta: existe uma maneira de fazer uma subconsulta para juntar o first_score e last_score em uma única consulta?

postgresql order-by

1 respostas

  1. Best Answer
    WITH t AS (
SELECT 1 user_id, '2019-05-15'::date AS created_at, 4 as score
UNION ALL
SELECT 1 user_id, '2019-05-13'::date AS created_at, 12 as score
UNION ALL
SELECT 2 user_id, '2019-05-15'::date AS created_at, 7 as score
UNION ALL
SELECT 3 user_id, '2019-05-13'::date AS created_at, 6 as score
)
SELECT
    md5(user_id::varchar) user_id,
    created_at signup_date,
    MIN(created_at) OVER (PARTITION BY user_id) first_score_created_at,
    FIRST_VALUE(score) OVER (PARTITION BY user_id ORDER BY created_at ASC) first_score,
    MAX(created_at) OVER (PARTITION BY user_id) last_score_created_at,
    FIRST_VALUE(score) OVER (PARTITION BY user_id ORDER BY created_at DESC) last_score
FROM t
    • 2

relate perguntas