筛选出间隔一小时内并且有2次以上操作的用户，求sql的思路

select  uid, DATE_FORMAT(created_at, '%Y-%m-%d %H') AS hour
from t
-- where action in ()
group by uid, DATE_FORMAT(created_at, '%Y-%m-%d %H') 
having count(*) > 1

SELECT
  DATE_FORMAT(`created_at`, '%Y/%m/%d %H') as `date_value`,
  COUNT(`id`) as `count_value`,
  *
FROM xxx
GROUP BY `date_value`, uid
HAVING count_value > 1
LIMIT 0, 100;

感觉可以考虑在保存时增加一个字段（recent_actions）

每次保存时查询一小时内是否有其他操作，有其他操作,recent_actions=其他操作数量（或者用redis缓存最后操作时间，应该可以提升性能）

统计时查询recent_atcions>=2的唯一用户就可以了

如果不允许保存时新增字段，感觉可以使用脚本遍历统计范围内每条数据（用sql join 范围内其他数据数量应该也能行），查询每条数据一小时范围内的其他操作数量，然后再筛选出符合的用户。

SELECT DISTINCT t2.uid from table t1 
LEFT JOIN table t2  on t2.uid = t1.uid 
where t2.created_at > t1.created_at
and t2.created_at <= DATE_ADD(t1.created_at, INTERVAL 1 HOUR

对于这个问题，你可以使用以下的 SQL 查询来实现筛选出间隔一小时内并且有2次以上操作的用户：

SELECT uid
FROM your_table
GROUP BY uid
HAVING COUNT(*) >= 2
   AND MIN(created_at) <= DATE_ADD(MAX(created_at), INTERVAL -1 HOUR);

这个查询首先使用 GROUP BY 将记录按照用户ID进行分组，然后使用 COUNT(*) 统计每个用户的操作次数。接着使用 HAVING 子句过滤出操作次数大于等于2的用户。最后，利用 MIN 和 MAX 函数结合 DATE_ADD 函数来检查最早和最晚的操作时间是否相隔一小时以内。

请将 your_table 替换为你实际使用的表名。

哇 大神真多！

拿走不谢！！！
SELECT uid, DATE_FORMAT (created_at, ‘% Y-% m-% d % h’) as group_time, count (‘group_time’) as num
FROM user_xxx
GROUP BY group_time, uid
HAVING num > 1
ORDER BY id DESC LIMIT 0, 100

chatgpt 给的答案。万事不决，就上gpt

use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

$interval = 1; // 间隔时间（单位：小时）
$minRecords = 2; // 最小记录数

$query = "
    SELECT uid, COUNT(*) AS record_count
    FROM your_table_name
    WHERE created_at >= ?
    GROUP BY uid
    HAVING COUNT(*) >= ?
";

$bindings = [
    Carbon::now()->subHours($interval),
    $minRecords
];

$users = DB::select($query, $bindings);

foreach ($users as $user) {
    echo "User with UID {$user->uid} has {$user->record_count} records within the past {$interval} hour(s).";
}

SELECT DISTINCT
    ( t1.uid ) AS uid 
FROM
    act AS t1
    JOIN act AS t2 ON t1.uid = t2.uid 
    AND ABS(
    UNIX_TIMESTAMP( t1.created_at ) - UNIX_TIMESTAMP( t2.created_at )) < 3600 
    AND t1.id != t2.id;

SELECT uid FROM your_table WHERE created_at >= NOW() - INTERVAL 1 HOUR GROUP BY uid HAVING COUNT(*) >= 2;

假设用的是laravel，有个UserLog模型，那么用下面的语句应该能查出来你要的效果。

$query = UserLog::select('uid', DB::raw('COUNT(*) as count'))
->where('created_at', '>=', now()->subHour())
->groupBy('uid')
->having('count', '>=', 2)
->get();

如果想要完整的SQL语句，加上下面的代码能显示出完整的sql语句

echo $query->toSql();