Postgresql 将购买与最近登录的设备相匹配

Question

在我的应用程序中，我将user_id、device_id（ifa）和登录日期的登录存储在名为sessios_logs的表中。我将购买user_id、金额和购买日期存储在称为购买的表中。

下面是这两个表的示例输出，我尝试匹配每次购买的session_logs中的相应 ifa 值。

session_logs

uid         ifa  date
7560168721  CCC   1
7560168721  AAA   3
7560168721  BBB   5
7560168721  AAA   8
7560168721  AAA   10

购买

uid       amount    date    <ifa>? 
7560168721   $1        2      CCC
7560168721   $9        4      AAA
7560168721   $5        7      BBB
7560168721   $4        11     AAA

那么，如何找到每次购买的最接近的登录日期和ifa值呢？

我不知道如何在检查session_logs中的最长时间并且登录日期早于购买日期的同时迭代每一行。

谢谢。

Answer 1

您可以使用其中一个窗口函数将购买日期与最近的会话日期相匹配。

SELECT uid, amount, date, ifa
FROM
    (SELECT
            t1.uid,
            t2.amount,
            t2.date AS date,
            ROW_NUMBER() OVER (PARTITION BY t1.uid, t2.date) AS rn,
            t1.ifa
      FROM
            session_logs t1
            JOIN purchases t2 ON
                 t1.uid = t2.uid AND
                 t1.date <= t2.date) x1
WHERE x1.rn = 1

Answer 2

您可以使用

窗口函数，但遗憾的是它们在WHERE部分中是不允许的，因此周围有一个包装器查询：

select
  uid,
  amount,
  date,
  ifa
from
(
  select
    l.uid,
    amount,
    p.date as date,
    max(l.date) over (partition by l.uid, p.date) as max_date,
    l.ifa
  from
    session_logs l
    join purchases p on
      l.uid = p.uid and
      l.date <= p.date
  -- where l_date = max(l.date) over (partition by l.uid, p.date)
) t
where l_date=max_date;

http://sqlfiddle.com/#!15/36185/9/0

Answer 3

select
    uid,
    amount,
    date,
    (
        select ifa
        from session_logs
        where
            uid = purchases.uid and
            date < purchases.date
        order by date desc
        limit 1
    )
from purchases;