S3 のサーバアクセスログから、Athena を使って日毎のリクエスト数を取得する

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

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