最佳答案
SELECT
*
FROM
(
SELECT
*,
row_number() over (
PARTITION BY market_id,
date( created_at )
ORDER BY
ABS(
UNIX_TIMESTAMP( created_at ) - UNIX_TIMESTAMP(
CONCAT( DATE_FORMAT( created_at, '%Y-%m-%d' ), ' 04:00:00' )))
) AS rn
FROM
sentiments
WHERE
created_at >= TIMESTAMP ( '2014-01-01' )
AND created_at < TIMESTAMP ( '2014-02-01' )
AND market_id = 1
) d
WHERE
rn = 1
讨论数量:
用created_at减去对应日期4点的差值取绝对值之后,然后按绝对值小到大排序,这个数据量,感觉应该先冗余一个差值绝对值字段 :speak_no_evil:
select * from (SELECT
*, ABS(
(
UNIX_TIMESTAMP(created_at) - UNIX_TIMESTAMP(
CONCAT(
DATE_FORMAT(created_at, "%Y-%m-%d"),
" 04:00:00"
)
)
)
) AS math_sub,
DATE_FORMAT(created_at, "%Y-%m-%d") as days
FROM
table_name
ORDER BY
math_sub ASC) as b
GROUP BY b.days
SELECT
*
FROM
(
SELECT
*,
row_number() over (
PARTITION BY market_id,
date( created_at )
ORDER BY
ABS(
UNIX_TIMESTAMP( created_at ) - UNIX_TIMESTAMP(
CONCAT( DATE_FORMAT( created_at, '%Y-%m-%d' ), ' 04:00:00' )))
) AS rn
FROM
sentiments
WHERE
created_at >= TIMESTAMP ( '2014-01-01' )
AND created_at < TIMESTAMP ( '2014-02-01' )
AND market_id = 1
) d
WHERE
rn = 1
推荐文章: