时间戳

首页 > 解决方案 > 用于创建具有不同时间戳和不同列映射的合并表的 SQL 查询

mysql - 用于创建具有不同时间戳和不同列映射的合并表的 SQL 查询

问题描述

我正在尝试编写一个复杂的 mySQL 查询，其中有 2 个表操作和收入我需要的是：

  1. 从拍卖表中取出位置、基于用户、cat_id、cat 和花费的邮政编码，并加入具有收入列的收入表，以便给定 cat_id、cat 和日期，我可以计算出每个“邮政”的回报生成。

复杂性：

用户是这里的唯一键

  1. 在拍卖表中有“已花费”列，但仅当“事件”列有“显示”但它有“猫”条目时才会填充。并且“cat_id”在除节目之外的任何事件中开始填充。所以需要从'cat'映射cat_id为事件'show'来获取那个cat_id的花费。

  2. 必须设置日期，以便在加入表格时，应比较时间戳正负 10 分钟。现在在我的查询中，我有 24 小时的持续时间

  3. 以递减顺序汇总邮政，以获得最高回报

**Auction Table**
dt                  user cat_id cat location  postal     event    spent
2020-11-01 22:12:25  1     0    A      US      X12        Show      2
2020-11-01 22:12:25  1     0    A      US      X12        Show      2 (duplicate also in table)
2020-11-01 22:12:25  1     6    A      US      X12        Mid      null
2020-11-01 22:13:20  2     0    B      UK      L23        Show      2
2020-11-01 22:15:24  2     3    B      UK      L23        End      null

**Revenue table**
dt                     user  cat_id   revenue
2020-11-01 22:14:45     1      6        null
2020-11-01 22:13:20     2      3        3

想要创建决赛桌（通过汇总每个“邮政”区域的收入）：

location   postal   spend  revenue returns
UK          X12      2       0        0
US          L23      2       3        3/2=1.5

我已经写了一个查询，但无法找出上述 3 个复杂性的解决方案：

Select s.location, s.postal, s.spend, e.revenue
From revenue e JOIN
     auction s
     on e.user = s.user
where  s.event in ('Mid','End','Show') and 
       TO_DATE(CAST(UNIX_TIMESTAMP(e.dt, 'y-M-d') AS TIMESTAMP)) = TO_DATE(CAST(UNIX_TIMESTAMP(s.dt, 'y-M-d') AS TIMESTAMP)) and
       s.cat_id in ('3') and
       s.cat = 'B'

任何建议都会有所帮助

标签： mysqlsqlrelational-database

解决方案

这回答了 MySQL 的问题，这是问题的原始标签，也是问题中提到的。

如果我理解正确，您的问题是在一个时间范围内“加入”。您可以使用相关子查询来做您想做的事情。然后剩下的就是聚合，我认为是：

select location, postal, max(spend), max(revenue)
from (select a.*,
             (select sum(r.revenue)
              from revenue r
              where r.user = a.user and
                    r.dte >= s.dt - interval 10 minute and
                    r.dte <= s.dte + interval 10 minute
             ) as revenue
      from auction a
      where s.event in ('Mid', 'End', 'Show') and 
            s.cat_id in (3) and
            s.cat = 'B'
     ) a
group by location, postal;

推荐阅读