左边连接两个表，基于时间

I am doing accumulation for data, based on 30 minutes for one day, the customers performed their transactions over this day. I have two tables, the first shows the customers behavior in whole day. the second table shows the customer behavior at night only. I want to make left join for two tables with time based: Is that possible to make? Actually, I tried to do that but I got duplication or cross products between two table.

我正在积累数据,基于一天30分钟,客户在这一天进行交易。我有两张桌子,第一张显示客户一整天的行为。第二个表格仅显示晚上的客户行为。我想基于时间对两个表进行左连接:这有可能吗?实际上,我试图这样做,但我在两个表之间有重复或交叉产品。

Note: I can make left join between two tables but is not time based.

注意:我可以在两个表之间进行左连接,但不是基于时间的。

Here clarification for the tables

这里澄清了表格

Table 1

Time_Minutes_Based|User_Id|Total_Of_Transactions|Total_of_outgOing
10:00:30          |   1   |           4         |         9
10:00:30          |   2   |          12         |         5
10:01:30          |   6   |           3         |         2
.                 |       |                     |
.                 |       |                     |
.                 |       |                     |
00:01:30          |   8   |           7         |         3

Table 2 for Night Transactions only:

表2仅适用于夜间交易:

Time_Minutes_Based|User_Id|Total_Of_Transactions_at_Night|Total_of_outgoing_at_Night
00:00:30          |   9   |                0             |              6
00:06:30          |   8   |                3             |              3
00:06:30          |   3   |                4             |              0
.                 |       |                              |
.                 |       |                              |
.                 |       |                              |
05:59:00          |   2   |                9             |              3

Expected Table:

Time_Minutes_Based|User_Id|Total_Of_Transactions|Total_of_outgoing|Time_Minutes_Based|User_Id|Total_Of_Transactions_at_Night|Total_of_outgoing_at_Night 
00:00:30          |  1 |        Null         |      Null       |      Null         |   3   |              1               |

1 个解决方案

#1

This should work, but Time_Minutes_Base of whole day table and NightOnly table must match at 100%.

这应该有效,但是Time_Minutes_Base的全天表和NightOnly表必须匹配100%。

00:00:30 <> 00:00:31


select 
      wd.Time_Minutes_Based
    , wd.User_Id
    , wd.Total_Of_Transactions
    , wd.Total_of_outgOing
    , no.Total_Of_Transactions_at_Night
    , no.Total_of_outgoing_at_Night
from
    WholeDay wd
    left join NightOnly no
where
    wd.UserId = no.UserId
    wd.Time_Minutes_Based = no.Time_Minutes_Based;

Another approach if you need to ensure the query returns all records.

另一种方法是,如果需要确保查询返回所有记录。

select 
      Time_Minutes_Based
    , User_Id
    , Total_Of_Transactions
    , wd.Total_of_outgOing
    , ''  as Total_Of_Transactions_at_Night
    , ''  as Total_of_outgoing_at_Night
from
    WholeDay
UNION ALL
select 
      Time_Minutes_Based
    , User_Id
    , '' as Total_Of_Transactions
    , '' as Total_of_outgOing
    , Total_Of_Transactions_at_Night
    , Total_of_outgoing_at_Night
from
    NightOnly;

#1