by ohworld5 [SQL Query] postgres [2019.02.26 13:57:53]
로그인 실패 테이블이 있구요.
컬럼으로 아이디, 시간, 아이피가 있습니다.
30분이내에 3회이상 로그인에 실패하면 lock을 걸게되는데(1시간후에 lock해제),
유저테이블에는 상태정보만 저장하고 있어서 몇번이나 lock걸렸는지는 알수가 없습니다.
로그인 실패 테이블상에서 lock횟수를 카운트하는 SQL을 알려주셨으면 좋겠습니다.
WITH X AS ( SELECT 'A' AS 아이디, TO_CHAR(SYSDATE - 60 / (24*60), 'YYYYMMDDHH24MISS') AS 시간 FROM DUAL UNION ALL -- 60분전 SELECT 'A' AS 아이디, TO_CHAR(SYSDATE - 30 / (24*60), 'YYYYMMDDHH24MISS') AS 시간 FROM DUAL UNION ALL -- 30분전 SELECT 'A' AS 아이디, TO_CHAR(SYSDATE - 10 / (24*60), 'YYYYMMDDHH24MISS') AS 시간 FROM DUAL UNION ALL -- 10분전 SELECT 'A' AS 아이디, TO_CHAR(SYSDATE - 5 / (24*60), 'YYYYMMDDHH24MISS') AS 시간 FROM DUAL UNION ALL -- 5분전 SELECT 'A' AS 아이디, TO_CHAR(SYSDATE - 1 / (24*60), 'YYYYMMDDHH24MISS') AS 시간 FROM DUAL -- 1분전 ) SELECT 아이디 , COUNT(*) AS 횟수 FROM X WHERE 시간 >= TO_CHAR(SYSDATE - 30 / (24*60), 'YYYYMMDDHH24MISS') GROUP BY 아이디
WITH T AS (SELECT 'A' USERID, '2019-01-01 01:00:00' REGDATE FROM DUAL UNION ALL SELECT 'A' USERID, '2019-01-01 01:29:59' REGDATE FROM DUAL UNION ALL SELECT 'A' USERID, '2019-01-01 01:59:58' REGDATE FROM DUAL UNION ALL SELECT 'A' USERID, '2019-01-01 04:00:00' REGDATE FROM DUAL UNION ALL SELECT 'A' USERID, '2019-01-01 04:30:00' REGDATE FROM DUAL UNION ALL SELECT 'A' USERID, '2019-01-01 04:59:58' REGDATE FROM DUAL UNION ALL SELECT 'A' USERID, '2019-01-01 05:29:57' REGDATE FROM DUAL UNION ALL SELECT 'A' USERID, '2019-01-01 07:00:00' REGDATE FROM DUAL UNION ALL SELECT 'A' USERID, '2019-01-02 01:00:00' REGDATE FROM DUAL) SELECT USERID, YMD, COUNT(1) AS CNT FROM (SELECT USERID, YMD, REGDATE, CHECK1, CHECK2, CHECK3 FROM (SELECT USERID , YMD , REGDATE , CASE WHEN REGDATE - CHK1_REGDATE < 30 / 60 / 24 THEN 1 END CHECK1 , CASE WHEN REGDATE - CHK2_REGDATE <= (90 / 60 / 24) - (2 / 60 / 60 / 24) THEN 1 END CHECK2 , CASE WHEN LAG(REGDATE - CHK1_REGDATE) OVER(PARTITION BY USERID, YMD ORDER BY REGDATE ASC) < 30 / 60 / 24 THEN 1 END CHECK3 FROM ( SELECT USERID , REGDATE , LAG(REGDATE, 1) OVER(PARTITION BY USERID, TRUNC(REGDATE) ORDER BY REGDATE ASC) CHK1_REGDATE , LAG(REGDATE, 2) OVER(PARTITION BY USERID, TRUNC(REGDATE) ORDER BY REGDATE ASC) CHK2_REGDATE , TRUNC(REGDATE) YMD FROM ( SELECT USERID, TO_DATE(REGDATE, 'YYYY-MM-DD HH24:MI:SS') REGDATE FROM T ) ))) WHERE CHECK1 + CHECK2 + CHECK3 = 3 GROUP BY USERID, YMD
오라클에서 만든 쿼리인데 postgresql 도 돌아갈 것 같습니다. 아니면 비슷한 방법으로 만들면 되지 싶습니다.
/*====================================== * PostgreSQL *======================================*/ WITH X AS ( SELECT 'A' AS 아이디, TO_CHAR(NOW() - interval '60 minutes', 'YYYYMMDDHH24MISS') AS 시간 UNION ALL -- 60분전 SELECT 'A' AS 아이디, TO_CHAR(NOW() - interval '30 minutes', 'YYYYMMDDHH24MISS') AS 시간 UNION ALL -- 30분전 SELECT 'A' AS 아이디, TO_CHAR(NOW() - interval '10 minutes', 'YYYYMMDDHH24MISS') AS 시간 UNION ALL -- 10분전 SELECT 'A' AS 아이디, TO_CHAR(NOW() - interval '5 minutes' , 'YYYYMMDDHH24MISS') AS 시간 UNION ALL -- 5분전 SELECT 'A' AS 아이디, TO_CHAR(NOW() - interval '1 minutes' , 'YYYYMMDDHH24MISS') AS 시간 -- 1분전 ) SELECT 아이디 , COUNT(*) AS 횟수 FROM X WHERE 시간 >= TO_CHAR(NOW() - interval '30 minutes', 'YYYYMMDDHH24MISS') GROUP BY 아이디
30분 이내 실패 횟수를 알아야 락을 걸 수 있겠지요.
30분 이내 실패 횟수 구하는 쿼리 알려 드린 거구요.
락 걸린 사용자의 락이 언제 걸린건지 확인하는 쿼리가 필요하다면?
로그인실패테이블이 어떨 떄 로그가 쌓이는지? 알아야 하구요.
락 상태에서도 실패시 계속 쌓이는지? 락 걸리기 전에 실패할 때만 쌓이는지? 등등
해당 정보를 가지고 락 시간을 구하는 것은 상당히 복잡하고 비효율 적인 쿼리가 될 수 있습니다.
로그인 할 때마다 이런 복잡한 쿼리가 도는 것은 좋지 않습니다.
락을 걸때 시간도 함께 저장한다면? 굳이 히스토리를 뒤질 필요가 없죠.
락을 거는 행위(DML)가 실제로 있는건지?
아니면 이것 조차도 별도 DML 없이 히스토리 뒤져서(SELECT) 락 여부를 판단해야 하는 것인지?
정확한 정보가 필요합니다.
예시 자료가 있으면 더 좋구요.