SQL 连接 2 个独立的日期列

发布于 2025-01-18 09:16:38 字数 822 浏览 0 评论 0原文

我很难将自己的问题缠住一个问题，而我越远，我就越想到了。

我有两个表格将用户访问到两个单独的应用程序中。为简单起见，假设只有2列，时间戳和用户ID（键）。每行都视为访问，因此从技术上讲是3列，因为有一个派遣访问。

我正在尝试创建一个表格，每行都记录用户ID，一天（TO_DATE格式），对APP 1的总访问以及当天对该用户的App 2的总访问。

问题是，当我在用户ID和日期上将表加在一起时，我会丢失数据。例如，如果用户在X上登录A的应用A，但没有在X上登录B，则加入UserId和Day会导致此记录被省略，因为仅在应用程序A的表中存在日期。

如何在最终表有单个日期列（用户ID，访问App a并访问App B）的位置设置此设置，无论用户是否在上述日访问了两个应用程序？

希望这是有道理的，乐于详尽。下面是我的SQL到目前为止的样子。任何想法都赞赏！

with app_a_visits as (
      select to_date(timestamp) as date, userid, count(*) as visits
      from app_a),

app_b_visits as (
  select to_date(timestamp) as date, userid, count(*) as visits
  from app_b)

select a.date, a.userid, a.visits as app_a_visits, b.visits as app_b_visits
from app_a_visits a
full outer join app_b_visits b on a.userid = b.user_id and a.date = b.date;

原文

I am having trouble wrapping my head around a problem and the more I think about it the further away I get.

I have two tables that log user visits into two separate applications. For simplicities sake, let's say there are only 2 columns, timestamp and userid (key). Each row counts as a visit, so technically 3 columns since there is one derived for visits.

I am trying to create a table that in each row records the userid, the day (to_date format), total visits to app 1 and total visits to app 2 for that user on that day.

The issue is, when I join the tables together on userid and date, I get missing data. For example, if a user logged into application A on day X, but did not log into application B on day X, then joining on userid and day causes this record to be omitted since the date only exists in Application A's table.

How can I set this up where the final table would have a singular date column, userid, visits to app A and visits to app B, regardless if the user visited both applications on said day?

Hope this made sense, happy to elaborate if needed. Below is sort of what my SQL looks like as of now. Any thoughts appreciated!

with app_a_visits as (
      select to_date(timestamp) as date, userid, count(*) as visits
      from app_a),

app_b_visits as (
  select to_date(timestamp) as date, userid, count(*) as visits
  from app_b)

select a.date, a.userid, a.visits as app_a_visits, b.visits as app_b_visits
from app_a_visits a
full outer join app_b_visits b on a.userid = b.user_id and a.date = b.date;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

是你 2025-01-25 09:16:38

使用 FULL OUTER JOIN 和 NVL/COALESCE

with app_a_visits(date, userid,visits) as (
    select * from values
    ('2022-01-01'::date, 1, 100),
    ('2022-01-03'::date, 1, 100),
    ('2022-01-05'::date, 1, 100)
), app_b_visits(date, userid,visits) as (
    select * from values
    ('2022-01-02'::date, 1, 200),
    ('2022-01-03'::date, 1, 200),
    ('2022-01-04'::date, 1, 200)
)
select 
    NVL(a.date, b.date) as date,
    NVL(a.userid, b.userid) as userid,
    a.visits as app_a_visits, 
    b.visits as app_b_visits
from app_a_visits a
full outer join app_b_visits b 
    on a.userid = b.userid and a.date = b.date
ORDER BY 1,2;

日期	USERID	APP_A_VISITS	APP_B_VISITS
2022-01-01	1	100	null
2022-01-02	1	null	200
2022-01-03	1	100	200
2022-01-04	1	null	200
2022-01-05	1	100	空

Use FULL OUTER JOIN and NVL/COALESCE

with app_a_visits(date, userid,visits) as (
    select * from values
    ('2022-01-01'::date, 1, 100),
    ('2022-01-03'::date, 1, 100),
    ('2022-01-05'::date, 1, 100)
), app_b_visits(date, userid,visits) as (
    select * from values
    ('2022-01-02'::date, 1, 200),
    ('2022-01-03'::date, 1, 200),
    ('2022-01-04'::date, 1, 200)
)
select 
    NVL(a.date, b.date) as date,
    NVL(a.userid, b.userid) as userid,
    a.visits as app_a_visits, 
    b.visits as app_b_visits
from app_a_visits a
full outer join app_b_visits b 
    on a.userid = b.userid and a.date = b.date
ORDER BY 1,2;