SQL:从不同单元格减去的复杂查询

Question

我有两个表，我想组合它们的数据。

第一个表

+------------+-----+------+-------+
| BusinessID | Lat | Long | Stars |
+------------+-----+------+-------+
| abc123     |  32 |   74 | 4.5   |
| abd123     |  32 |   75 | 4     |
| abe123     |  33 |   76 | 3     |
+------------+-----+------+-------+

第二个表是：

+------------+-----+------+-------+
| BusinessID | day | time | count |
+------------+-----+------+-------+
| abc123     |   1 |   14 |     5 |
| abc123     |   1 |   15 |     6 |
| abc123     |   2 |   13 |     1 |
| abd123     |   4 |   12 |     4 |
| abd123     |   4 |   13 |     8 |
| abd123     |   5 |   11 |     2 |
+------------+-----+------+-------+

所以我想要做的是找到在特定半径内的所有企业，并且在下一个小时内比当前时间有更多的签到。

所以结果是

+------------+
| BusinessID |
+------------+
| abd123     |
| abc123     |
+------------+

因为他们在接下来的一个小时内比前一个小时有更多的签到(6 > 5，8> 4)

更重要的是，如果结果按照它们在签到数量上的不同进行排序，这将是有帮助的。例如。(8-4>6-5)

SELECT *
FROM table2 t2
WHERE t2.BusinessID IN (
    SELECT t1.BusinessID 
    FROM table1 t1
    WHERE earth_box(ll_to_earth(32, 74), 4000/1.609) @> ll_to_earth(Lat, Long)
    ORDER by earth_distance(ll_to_earth(32, 74), ll_to_earth(Lat, Long)), stars DESC
    ) AND checkin_day = 1 AND checkin_time = 14;

从上面的查询中，我可以找到radius中的企业，然后找到它们在指定时间内的签到。例如。14.我现在要做的是找出(相同业务的) 15小时内的签到次数，看看签到次数是否比以前多了。

Answer 1

我认为你想要这样的东西：

SELECT
    t1.BusinessID
FROM
    table1 t1
    JOIN
    (SELECT
        *,
        "count" - LAG("count") OVER (PARTITION BY BusinessID, "day" ORDER BY "time") "grow"
    FROM 
        table2
    WHERE
        /* Some condition on table2 */) t2
    ON t1.BusinessID = t2.BusinessID AND t2.grow > 0
WHERE
        /* Some condition on table1 */
ORDER BY
    t2.grow DESC;