CREATE TABLE `tb` (
`时间` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`小数` decimal(7,5) NOT NULL DEFAULT '0.00000'
插入数据：
INSERT INTO `tb`(`时间`, `小数`) VALUES
('2022-05-30 00:00:01',0.07523),
('2022-05-30 00:01:00',0.04416),
('2022-05-30 00:02:01',0.04429),
('2022-05-30 00:03:00',0.04348),
('2022-05-30 00:04:01',0.02853),
('2022-05-30 00:05:01',0.01429),
('2022-05-30 00:06:01',0.01409),
('2022-05-30 00:07:01',0.01517),
('2022-05-30 00:08:01',0.04371),
('2022-05-30 00:09:01',0.06100),
('2022-05-30 00:10:01',0.08211),
('2022-05-30 00:11:01',0.08391),
('2022-05-30 00:12:01',0.13065),
('2022-05-30 00:13:01',0.17940),
('2022-05-30 00:14:01',0.21385)
使用如下SQL语句实现：
SELECT substr(MAX(时间),12,5) as 时间,AVG(小数) as 每组平均数 FROM `tb` group by floor(MINUTE( 时间 )/ 10 ) * 10
或者如下语句实现：
SELECT date_format(MAX(时间),'%H:%i') as 时间,AVG(小数) as 每组平均数 FROM `tb` group by floor(MINUTE( 时间 )/ 10 ) * 10
按每10分钟分组最终结果如下：
sqlserver语句实现可以用：
select CONVERT(varchar(5),max([时间 ]),8) as 时间 ,avg([小数]) as 每组平均数 from [tb] group by cast(floor(cast([时间] as float)*24*60/10)*10/60/24 as smalldatetime)
结果如下：
1 declare @flag1 varchar(32) set @flag1='c115:'
2 declare @flag2 varchar(32) set @flag2='c116:'
3 select SampleTime ,
4 replace ( substring ( SampleValues , charindex ( ''+@flag1+'' , Sam...
select to_char((daybegin + (level - 1) * 5 / 1440), 'yyyy-mm-dd hh24:mi:ss') as newdaybegin
from (select (trunc(sysdate, 'dd')) as daybegin from dual)
connect by level <= 288
后面话单报表开发要用到，记录一下。
select * from (
select DAYS, ROW_NUMBER()OVER(ORDER BY DAYS) rn from (
SELECT to_date('2014-09-09 12:00:00', 'yyyy-mm-dd hh24:mi:ss') + ROWNUM/24/60 - 1/24/60 DAYS FROM DUAL
CONNECT BY ROWNUM
开发中遇到过问题就是对时间以半个小时分钟分组，如统计08：00-08：30的人数，08：30-09：00的人数，貌似sql中没有这样的函数吧，直接从数据库里查出来，在java里分组也太low了吧
想到方法1 自定义函数，自己实现时间的半个小时转换，统计时调用函数
CREATE FUNCTION `date_half_hour_format`(in_date TIMESTAMP) RETURN
SQL语句获得每隔一段时间的记录
间隔三十
分钟：
SELECT TO_CHAR(TRUNC(SYSDATE, 'DD') + (5 / (24 * 60) * ROWNUM),
'YYYY-MM-DD hh24:MI:SS')
FROM 表名
WHERE ROWNUM <= 1440 / 30;
间隔
五分钟：
SELECT TO_CHAR(TRUNC(SYSDATE, 'DD') + (5 / (24 * 60) * ROWNUM),
SQL分组统计是一种将数据按照某些标准进行分组，并对每个分组进行计算的方法。常用的分组统计函数包括COUNT、SUM、AVG、MAX和MIN等。以下是一个示例代码，假设有一个订单表orders，其中包含订单号(order_id)、客户ID(customer_id)和订单金额(amount)等字段：
SELECT customer_id, COUNT(order_id), SUM(amount), AVG(amount), MAX(amount), MIN(amount)
FROM orders
GROUP BY customer_id;
此代码将按照客户ID对订单表进行分组，并统计每个客户ID的订单数、订单金额总和、订单金额平均值、最大订单金额和最小订单金额等信息。
