mysql - 从单个表获取 MAX、MIN、SUM、AVERAGE 并创建报告

Question

我正在使用间隔(比如每小时)存储来自文本文件的平面文件数据我得到的数据如下

日期(日期时间)，总时间(varchar(20))，总计数(BigInt)，最短时间 (varchar(20))，最长时间 (varchar(20))。

我正在寻找类似这样的报告

到目前为止我的SQL

select 
  sum(
    (CASE WHEN (isnumeric(TotalTime) = 1) 
     THEN CAST(TotalTime AS bigint)
     ELSE 0
     END)
  ) as 'TotalTime',
  sum(TotalCount) as 'TotalCount',
  AVG(
    (CASE WHEN (isnumeric(MinTime) = 1) 
    THEN CAST(MinTime AS bigint)
    ELSE 0
    END)
  ) as 'MinAvgTime',
  AVG(
    (CASE WHEN (isnumeric(MaxTime) = 1) 
    THEN CAST(MaxTime AS bigint)
    ELSE 0
    END)
  ) as 'MaxAvgTime',
  dateadd(DAY,0, datediff(day,0, InputDate)) as created
from MYTABLE
group by dateadd(DAY,0, datediff(day,0, InputDate))

试图弄清楚是否有任何方法可以在不使用 LINQ 的情况下获得该输出..是否有任何方法可以直接从 SQL 查询中获取它

Answer 1

你从下面的 SQL 得到什么结果:

SELECT
  date(`day`) AS created,
  sum(TotalTime) AS TotalTime,
  sum(TotalCount) AS TotalCount,
  avg(MinTime) AS MinAvgTime,
  avg(MaxTime) AS MaxAvgTime
FROM MYTABLE
GROUP BY 1;