mysql 按小时聚合多个列

发布于 2024-09-25 08:31:06 字数 1321 浏览 2 评论 0原文

快速浏览了一下，却找不到我的问题了。所以我希望这里有人能提供帮助。我有一张大表，其结构与下面类似。

日期时间 |入站用户 ID |出站用户 ID |客户用户 ID |成本|分钟 |帐号

我试图将上述数据按小时分组，并将结果存储在另一个具有与下面类似结构的表中。

日期 |小时从 |小时至 |总和 |总成本 |出站用户 ID |客户用户 ID |帐号

我已经编写了以下代码/查询来执行此操作，但它不太正确。基本上，该组并不是对所有数据进行汇总。例如，当查看新创建的表时，我拥有所有 OutboundUserID 数据，但没有 CustomerUserID 数据。

    for(int i=0;i <=23;i++)
    {
        for(int j=1 ; j <=31 ; j++)
        {
            String Time = String.format("%02d", i);

            query = "INSERT DELAYED INTO correlated_calls_hourly " +
                    " SELECT DateOnly,HourFrom,HourTo,DURATION,CALLCOUNT,`Price Group ID`,INBOUNDCOST,INBOUNDREVENUE,`InboundCustomerID`,OUTBOUNDCOST,OUTBOUNDREVENUE,`Outbound Customer ID`,`Customer ID`,`Account Number`,`Service` FROM" +
                    " ("+
                        " SELECT "+
                        /* snip sum(),case and date/time part of query */
                        " GROUP BY  DateOnly,`InboundCustomerID`,`OutboundCustomerID`,`CustomerID`,`AccountNumber`,`PriceGroupID`,`Service`" +
                    " )a ";
        }
    }

我已经采取了创建三个不同表的步骤，以按 OutboundUserID/InboundUserID/CustomerUserID 分别汇总数据。但如果我能让这张汇总表正常工作，那就优雅多了。

如果有人能帮助我想出一种通过单个 sql 查询获得所需结果的方法，我将不胜感激。

预先非常感谢

艾伦

原文

Had a quick look through and couldn't find my question again. So I'm hoping someone here can help.
I have a large table with a similar structure to the below.

I'm trying to group the above data into hourly chunks and store the result in another table with a similar structure to below.

I've written the following code / query in order to do this but it's not quite right. Basically the group by means not all the data gets summarised. For instance when looking at the newly created table I have all the OutboundUserID data, but none of the CustomerUserID data.

    for(int i=0;i <=23;i++)
    {
        for(int j=1 ; j <=31 ; j++)
        {
            String Time = String.format("%02d", i);

            query = "INSERT DELAYED INTO correlated_calls_hourly " +
                    " SELECT DateOnly,HourFrom,HourTo,DURATION,CALLCOUNT,`Price Group ID`,INBOUNDCOST,INBOUNDREVENUE,`InboundCustomerID`,OUTBOUNDCOST,OUTBOUNDREVENUE,`Outbound Customer ID`,`Customer ID`,`Account Number`,`Service` FROM" +
                    " ("+
                        " SELECT "+
                        /* snip sum(),case and date/time part of query */
                        " GROUP BY  DateOnly,`InboundCustomerID`,`OutboundCustomerID`,`CustomerID`,`AccountNumber`,`PriceGroupID`,`Service`" +
                    " )a ";
        }
    }

I've already taken the step of creating three different tables to summarise the data separately by OutboundUserID/ InboundUserID / CustomerUserID. But it would be a lot more elegant if I could just get this one summarised table working correctly.

If anyone could help me think of a way to get the desired result via a single sql query I'd be greatly appreciative.

Many thanks in advance

Alan

分享到QQ

分享到微博

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

发布评论

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

挽梦忆笙歌 2024-10-02 08:31:06

像这样的东西会起作用吗？

INSERT INTO correlated_calls_hourly (Date, HourFrom, HourTo, SumMins, SumCost, OutboundUserID, CustomerUserID, AccountNumber)
SELECT 
  CONVERT(varchar(8), GETDATE(), 112),
  hour(DateTime),
  hour(DateTime) + 1, 
  SUM(Mins),
  SUM(Cost),
  OutboundUserID,
  CustomerUserID,
  AccountNumber
GROUP BY
  CONVERT(varchar(8), GETDATE(), 112),
  hour(DateTime)
  OutboundUserID,
  CustomerUserID,
  AccountNumber

转换函数将日期时间转换为日期。您可能需要对其进行一些调整以适应您的 RDBMS，但我看不出有任何理由不起作用。

Would something like this work?

INSERT INTO correlated_calls_hourly (Date, HourFrom, HourTo, SumMins, SumCost, OutboundUserID, CustomerUserID, AccountNumber)
SELECT 
  CONVERT(varchar(8), GETDATE(), 112),
  hour(DateTime),
  hour(DateTime) + 1, 
  SUM(Mins),
  SUM(Cost),
  OutboundUserID,
  CustomerUserID,
  AccountNumber
GROUP BY
  CONVERT(varchar(8), GETDATE(), 112),
  hour(DateTime)
  OutboundUserID,
  CustomerUserID,
  AccountNumber

The convert function is convert the datetime to just a date. You'd probably need to tweak it a little to suit your RDBMS but I can't see any reason why that wouldn't work.

回复收藏 0 原文