时间戳

首页 > 解决方案 > SQL 分组依据和日期间隔

sql - SQL 分组依据和日期间隔

问题描述

我有两个这样的表：

                  TABLE1 (SENDS)
 ------------------------------------------------
| ReceiptId | JobId |  SK  |       LogDate       |
|-----------|-------|------|---------------------|
| 0000001   | 67789 | 4151 | 6/4/2018 2:31:44 AM |
|-----------|-------|------|---------------------|
| 0000002   | 67789 | 4151 | 6/4/2018 8:25:50 AM |
|-----------|-------|------|---------------------|
| 0000003   | 67789 | 4151 | 6/4/2018 7:42:09 PM |
 ------------------------------------------------


           TABLE2 (CLICKS)
 ------------------------------------
| JobId |  SK  |       LogDate       |
|-------|------|---------------------|
| 67789 | 4151 | 6/4/2018 4:51:23 AM |
|-------|------|---------------------|
| 67789 | 4151 | 6/4/2018 5:32:52 AM |
|-------|------|---------------------|
| 67789 | 4151 | 6/4/2018 7:12:03 AM |
|-------|------|---------------------|
| 67789 | 4151 | 6/4/2018 5:14:37 PM |
|-------|------|---------------------|
| 67789 | 4151 | 6/4/2018 6:07:12 PM |
|-------|------|---------------------|
| 67789 | 4151 | 6/4/2018 9:46:52 PM |
 ------------------------------------

我需要做一个 SQL 查询来提取ReceiptIdfromTABLE1和记录的计数 where ( TABLE1.JobId = TABLE2.JobId& TABLE1.SK = TABLE2.SK) and TABLE2.LogDateis between TABLE1.LogDateand next high TABLE1.LogDate.

所以这个例子的表格结果是：

           RESULT TABLE
 -----------------------------------------
| ReceiptId | JobId |  SK  | Clicks Count |
|-----------|-------|------|--------------|
| 0000001   | 67789 | 4151 |      3       |
|-----------|-------|------|--------------|
| 0000002   | 67789 | 4151 |      2       |
|-----------|-------|------|--------------|
| 0000003   | 67789 | 4151 |      1       |
 -----------------------------------------

非常感谢您提前。

标签： sql

解决方案

似乎您想离开加入clicks，sends然后过滤logdateand 使用GROUP BYand count()。

但是您的描述和您想要的示例输出彼此不一致。

因此，以下内容得到了您的描述：

SELECT s.receiptid,
       s.jobid,
       s.sk,
       count(*)
       FROM sends s
            LEFT JOIN clicks c
                      ON c.jobid = s.jobid
                         AND c.sk = s.sk
       WHERE c.logdate >= s.logdate
             AND c.logdate <= (SELECT min(ss.logdate)
                                      FROM sends ss
                                      WHERE ss.logdate > s.logdate
                                            AND ss.jobid = s.jobid
                                            AND ss.sk = s.sk)
       GROUP BY s.receiptid,
                s.jobid,
                s.sk;

以下得到您想要的示例输出：

SELECT s.receiptid,
       s.jobid,
       s.sk,
       count(*)
       FROM sends s
            LEFT JOIN clicks c
                      ON c.jobid = s.jobid
                         AND c.sk = s.sk
       WHERE c.logdate >= s.logdate
             AND c.logdate <= coalesce((SELECT min(ss.logdate)
                                               FROM sends ss
                                               WHERE ss.logdate > s.logdate
                                                     AND ss.jobid = s.jobid
                                                     AND ss.sk = s.sk),
                                       c.logdate)
       GROUP BY s.receiptid,
                s.jobid,
                s.sk;

推荐阅读