热衷于使用间隔 2 秒的 datetime 分组吗？

发布于 2024-12-28 17:23:19 字数 621 浏览 3 评论 0原文

我有一个包含数据的表：

id          created              price    amount
2204155 2011-12-15 17:08:11.163   71       54150
2204156 2011-12-15 17:08:11.177   71       54150
2204157 2011-12-15 17:08:11.177   71       54150
2204158 2011-12-15 17:08:11.177   71       54150

我有一个 sql 查询：

select created, price, amount 
  from table 
  group by created, price, amount

结果：

created                 price   amount
2011-12-15 17:08:11.163   71    54150
2011-12-15 17:08:11.177   71    54150

我想接收单行。是否可以在按 2 秒间隙创建的节定义中适应同一组？

谢谢

原文

I have a table with data:

id          created              price    amount
2204155 2011-12-15 17:08:11.163   71       54150
2204156 2011-12-15 17:08:11.177   71       54150
2204157 2011-12-15 17:08:11.177   71       54150
2204158 2011-12-15 17:08:11.177   71       54150

I have a sql query:

select created, price, amount 
  from table 
  group by created, price, amount

Result:

created                 price   amount
2011-12-15 17:08:11.163   71    54150
2011-12-15 17:08:11.177   71    54150

I'd like to receive single line.
Is it possible in group by section define created with a gap of 2 seconds to fit in same group?

Thank you

分享到QQ

分享到微博

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

发布评论

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

落花随流水 2025-01-04 17:23:19

SELECT 
    DATEADD(ms, -DATEPART(ms, created), created) AS created, 
    price, 
    amount 
FROM MyTable 
GROUP BY 
    DATEADD(ms, -DATEPART(ms, created), created), 
    price, 
    amount

SELECT 
    DATEADD(ms, -DATEPART(ms, created), created) AS created, 
    price, 
    amount 
FROM MyTable 
GROUP BY 
    DATEADD(ms, -DATEPART(ms, created), created), 
    price, 
    amount

回复收藏 0 原文

み青杉依旧 2025-01-04 17:23:19

基础数学告诉我们，如果有一个偶数，则加或减 1 会产生一个奇数。推而广之，任何两个相邻的奇数彼此相差+/-2；例如，3秒和5秒之间的间隔是2秒。

稍微高级一点的数学教给我们模数算术；为了这个答案，您真正需要知道的是任何数字 % 2（% 符号通常用于表示 mod 运算符）将返回1 或 0，表示该数字是偶数还是奇数。

这意味着我们可以创建一个语句，表示“获取datetime”列的第二个值并检查它是偶数还是奇数。如果是偶数，则在datetime值上加1秒并输出；如果是奇数，则输出未更改的 datetime

这将生成一个输出，其中每个 datetime 值都向上舍入到最接近的奇数秒，这意味着 GROUP BY该 datetime 值上的 code> 将生成两秒间隔的聚合。

示例代码如下：

declare @testing_table table (amount int, whentime datetime)

insert into @testing_table
select * from 
(
select 1 as amount, '2012-01-25 16:53:44.437' as whentime

union
select 2, '2012-01-25 16:53:45.437'

union
select 3, '2012-01-25 16:53:46.437'

union
select 5, '2012-01-25 16:53:47.437'

union
select 7, '2012-01-25 16:53:48.437'

union 
select 11, '2012-01-25 16:53:49.437'

) workingtbl



select SUM(amount), grouptime
from
(
    select amount, case when isodd = 0 then DATEADD(second, 1, whentime) else whentime end as grouptime
    from
    (
        SELECT *, datepart(second,whentime)%2 as isOdd
        from
        @testing_table
    ) oddparse
)groupsum
group by grouptime

初始输入：

amount  whentime
1       2012-01-25 16:53:44.437
2       2012-01-25 16:53:45.437
3       2012-01-25 16:53:46.437
5       2012-01-25 16:53:47.437
7       2012-01-25 16:53:48.437
11      2012-01-25 16:53:49.437

最终输出：

(No column name)    grouptime
3                   2012-01-25 16:53:45.437
8                   2012-01-25 16:53:47.437
18                  2012-01-25 16:53:49.437

Basic math teaches us that if you have an even number, adding or subtracting 1 from it produces an odd number. By extension, any two adjacent odd numbers are +/- 2 from each other; for example, the gap between 3 seconds and 5 seconds is 2 seconds.

Slightly more advanced math teaches us about Modular arithmetic; for the sake of this answer, all you really need to know is that any number % 2 (the % symbol is often used to represent the mod operator) will return either a 1 or a 0, indicating whether the number is even or odd.

What this means is that we can create a statement that says 'Take the second value of a datetime column and check to see whether it is even or odd. If it is even, add 1 second to the datetime value and output it; if it is odd, output the unchanged datetime

This produces an output where each datetime value is rounded up to the nearest odd second, meaning that a GROUP BY on that datetime value will produce aggregates for two-second intervals.

Sample code follows:

declare @testing_table table (amount int, whentime datetime)

insert into @testing_table
select * from 
(
select 1 as amount, '2012-01-25 16:53:44.437' as whentime

union
select 2, '2012-01-25 16:53:45.437'

union
select 3, '2012-01-25 16:53:46.437'

union
select 5, '2012-01-25 16:53:47.437'

union
select 7, '2012-01-25 16:53:48.437'

union 
select 11, '2012-01-25 16:53:49.437'

) workingtbl



select SUM(amount), grouptime
from
(
    select amount, case when isodd = 0 then DATEADD(second, 1, whentime) else whentime end as grouptime
    from
    (
        SELECT *, datepart(second,whentime)%2 as isOdd
        from
        @testing_table
    ) oddparse
)groupsum
group by grouptime

Initial input:

amount  whentime
1       2012-01-25 16:53:44.437
2       2012-01-25 16:53:45.437
3       2012-01-25 16:53:46.437
5       2012-01-25 16:53:47.437
7       2012-01-25 16:53:48.437
11      2012-01-25 16:53:49.437

Final output:

(No column name)    grouptime
3                   2012-01-25 16:53:45.437
8                   2012-01-25 16:53:47.437
18                  2012-01-25 16:53:49.437

回复收藏 0 原文

浪菊怪哟 2025-01-04 17:23:19

不确定如何在 SQL Server 中具体执行此操作，但在 MySQL 中，您不仅可以使用 DATE_FORMAT() 来 SELECT 特定格式的日期，还可以对日期进行分组。就您而言，我想您希望将日期格式化为 2011-12-15 17:08 之类的结构（例如 YYYY-MM-DD HH:MM）它将按分钟分组。但不知道如何将其分解为两秒钟。