查询某天某个人消费总和

select DATE_FORMAT( FROM_UNIXTIME( date, '%Y%m%d%H%i%S' ), '%Y-%m-%d %H:%i:%S') as date from test where id = 1

查询每个人某个时间范围消费总和

image.png

select uid, sum(price) 
from test 
where DATE_FORMAT( FROM_UNIXTIME( date, '%Y%m%d%H%i%S' ), '%Y-%m-%d %H:%i:%S') < "2019-03-01 00:00:00" 
group by uid

image.png

查询一天中每个小时的数量

SELECT
    HOUR (FROM_UNIXTIME(date)) AS Hour,
    count(*) AS Count
FROM
    test
WHERE
    FROM_UNIXTIME(date, '%Y-%m-%d') = '2019-02-05'
GROUP BY
    HOUR (FROM_UNIXTIME(date))
ORDER BY
    HOUR (FROM_UNIXTIME(date));

image.png