DB、テーブルは作成済みの前提

GET、SELECT、他のすべてのリクエスト

SELECT regexp_extract(time, '\d{2}/\w+/\d{4}') AS by_day 
, count(*) AS cnt
FROM s3_accesslog
WHERE  (operation NOT LIKE '%PUT%'
        AND operation NOT LIKE '%POST%'
        AND operation NOT LIKE '%COPY%'
        AND  operation NOT LIKE '%LIST%')
GROUP BY regexp_extract(time,'\d{2}/\w+/\d{4}')
ORDER BY by_day

PUT、COPY、POST、LIST リクエスト

SELECT regexp_extract(time, '\d{2}/\w+/\d{4}') AS by_day 
, count(*) AS cnt
FROM s3_accesslog
WHERE  (operation LIKE '%PUT%'
        OR operation LIKE '%POST%'
        OR operation LIKE '%COPY%'
        OR  operation LIKE '%LIST%')
GROUP BY regexp_extract(time,'\d{2}/\w+/\d{4}')
ORDER BY by_day

参考サイト

Amazon Athena S3のサーバアクセスログでS3署名バージョンを確認してみた｜ Developers.IO
Amazon Athena の Presto 関数 - Amazon Athena