SQL을 통해 최소 순차적 액세스 기간을 결정하시겠습니까?
다음 사용자 기록 표에는 지정된 사용자가 웹 사이트에 액세스한 매일의 레코드가 하나 있습니다(24시간 UTC 기간 동안).수천 개의 레코드가 있지만 사용자당 하루에 하나의 레코드만 있습니다.사용자가 해당 날짜에 웹 사이트에 액세스하지 않은 경우 레코드가 생성되지 않습니다.
ID 사용자 ID 생성 날짜------ ------ ------------750997 12 2009-07-07 18:42:20.723750998 15 2009-07-07 18:42:20.927751000 19 2009-07-07 18:42:22.283
제가 찾고 있는 것은 성능이 좋은 이 테이블의 SQL 쿼리로, 어떤 사용자 ID가 하루도 놓치지 않고 웹 사이트에 계속해서 액세스했는지 알려줍니다.
즉, 이 표에 순차(일전 또는 일후) 날짜가 있는 레코드가 몇 개 있습니까?시퀀스에서 누락된 날짜가 있으면 시퀀스가 중단되고 1에서 다시 시작됩니다. 여기서 공백 없이 연속 일수를 달성한 사용자를 찾습니다.
물론 이 쿼리와 특정 스택 오버플로 배지 간의 유사성은 순전히 우연의 일치입니다.:)
(그리고 앞의 문장이 세미콜론으로 끝났는지 확인하십시오):
WITH numberedrows
AS (SELECT ROW_NUMBER() OVER (PARTITION BY UserID
ORDER BY CreationDate)
- DATEDIFF(day,'19000101',CreationDate) AS TheOffset,
CreationDate,
UserID
FROM tablename)
SELECT MIN(CreationDate),
MAX(CreationDate),
COUNT(*) AS NumConsecutiveDays,
UserID
FROM numberedrows
GROUP BY UserID,
TheOffset
날짜 목록(숫자)과 row_number가 있으면 누락된 날짜가 이 두 목록 사이의 오프셋을 약간 더 크게 만든다는 개념입니다.따라서 일관된 오프셋을 가진 범위를 찾고 있습니다.
마지막에 "ORDER BY NumContinuousDays Desc"를 사용하거나 임계값에 대해 "HAVE Count(*) > 14"라고 말할 수 있습니다.
하지만 나는 이것을 테스트해 본 적이 없습니다. 그냥 즉석에서 쓰는 것뿐입니다.SQL 2005 등에서 작동할 수 있기를 바랍니다.
...테이블 이름의 인덱스(UserID, CreationDate)를 사용하면 매우 유용할 것입니다.
편집: 오프셋은 예약된 단어이므로 대신 오프셋을 사용했습니다.
편집: COUNT(*)를 사용하자는 제안은 매우 유효합니다. 애초에 그렇게 했어야 했지만 실제로는 생각하지 않았습니다.이전에는 datediff(day, min(CreationDate), max(CreationDate)를 대신 사용했습니다.
롭
답은 분명히 다음과 같습니다.
SELECT DISTINCT UserId
FROM UserHistory uh1
WHERE (
SELECT COUNT(*)
FROM UserHistory uh2
WHERE uh2.CreationDate
BETWEEN uh1.CreationDate AND DATEADD(d, @days, uh1.CreationDate)
) = @days OR UserId = 52551
편집:
좋아요, 제 진지한 대답은 이렇습니다.
DECLARE @days int
DECLARE @seconds bigint
SET @days = 30
SET @seconds = (@days * 24 * 60 * 60) - 1
SELECT DISTINCT UserId
FROM (
SELECT uh1.UserId, Count(uh1.Id) as Conseq
FROM UserHistory uh1
INNER JOIN UserHistory uh2 ON uh2.CreationDate
BETWEEN uh1.CreationDate AND
DATEADD(s, @seconds, DATEADD(dd, DATEDIFF(dd, 0, uh1.CreationDate), 0))
AND uh1.UserId = uh2.UserId
GROUP BY uh1.Id, uh1.UserId
) as Tbl
WHERE Conseq >= @days
편집:
[제프 앳우드]이것은 매우 빠른 해결책이며 받아들여질 가치가 있지만, Rob Farley의 해결책은 또한 훌륭하고 거의 틀림없이 훨씬 더 빠릅니다(!).여러분들도 많이 봐주세요.
스키마를 할 수 것이 .LongestStreak다음으로 끝나는 연속 일 수로 설정한 테이블로CreationDate로그인 시 테이블을 쉽게 업데이트할 수 있습니다(이미 수행 중인 작업과 유사하게, 현재 날짜의 행이 없는 경우 전날 행이 있는지 확인합니다).참일 경우, 다음 값을 증가시킵니다.LongestStreak 새행서에로않, 그면으지 1다니설정합렇▁in
이 열을 추가하면 쿼리가 분명해집니다.
if exists(select * from table
where LongestStreak >= 30 and UserId = @UserId)
-- award the Woot badge.
표현력이 좋은 SQL은 다음과 같습니다.
select
userId,
dbo.MaxConsecutiveDates(CreationDate) as blah
from
dbo.Logins
group by
userId
사용자가 정의한 집계 함수가 있다고 가정하면 다음과 같습니다(이 함수는 버그입니다).
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Runtime.InteropServices;
namespace SqlServerProject1
{
[StructLayout(LayoutKind.Sequential)]
[Serializable]
internal struct MaxConsecutiveState
{
public int CurrentSequentialDays;
public int MaxSequentialDays;
public SqlDateTime LastDate;
}
[Serializable]
[SqlUserDefinedAggregate(
Format.Native,
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false) //optimizer property
]
[StructLayout(LayoutKind.Sequential)]
public class MaxConsecutiveDates
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private MaxConsecutiveState _intermediateResult;
/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
_intermediateResult = new MaxConsecutiveState { LastDate = SqlDateTime.MinValue, CurrentSequentialDays = 0, MaxSequentialDays = 0 };
}
/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlDateTime value)
{
if (value.IsNull)
{
return;
}
int sequentialDays = _intermediateResult.CurrentSequentialDays;
int maxSequentialDays = _intermediateResult.MaxSequentialDays;
DateTime currentDate = value.Value.Date;
if (currentDate.AddDays(-1).Equals(new DateTime(_intermediateResult.LastDate.TimeTicks)))
sequentialDays++;
else
{
maxSequentialDays = Math.Max(sequentialDays, maxSequentialDays);
sequentialDays = 1;
}
_intermediateResult = new MaxConsecutiveState
{
CurrentSequentialDays = sequentialDays,
LastDate = currentDate,
MaxSequentialDays = maxSequentialDays
};
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(MaxConsecutiveDates other)
{
// add stuff for two separate calculations
}
/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlInt32 Terminate()
{
int max = Math.Max((int) ((sbyte) _intermediateResult.CurrentSequentialDays), (sbyte) _intermediateResult.MaxSequentialDays);
return new SqlInt32(max);
}
}
}
n일 이상 지속하려면 행이 필요하다는 사실을 활용할 수 있을 것으로 보입니다.
그래서 다음과 같은 것이 있습니다.
SELECT users.UserId, count(1) as cnt
FROM users
WHERE users.CreationDate > now() - INTERVAL 30 DAY
GROUP BY UserId
HAVING cnt = 30
단일 SQL 쿼리로 이 작업을 수행하는 것은 저에게 너무 복잡해 보입니다.이 답을 두 부분으로 나누어 설명하겠습니다.
- 지금까지 수행했어야 하는 작업과 지금 시작해야 하는 작업:
현재 로그인한 모든 사용자를 확인한 다음 카운터가 있으면 카운터를 증분하고 없으면 0으로 설정하는 일일 크론 작업을 실행합니다. - 지금 해야 할 일:
웹 사이트를 실행하지 않고 당분간 필요하지 않은 서버로 이 테이블을 내보냅니다.;)
사용자별로 정렬한 다음 날짜를 지정합니다.
순차적으로 검사하고, 카운터를 유지합니다.
재귀 CTE(SQL Server 2005+)를 사용할 수 있습니다.
WITH recur_date AS (
SELECT t.userid,
t.creationDate,
DATEADD(day, 1, t.created) 'nextDay',
1 'level'
FROM TABLE t
UNION ALL
SELECT t.userid,
t.creationDate,
DATEADD(day, 1, t.created) 'nextDay',
rd.level + 1 'level'
FROM TABLE t
JOIN recur_date rd on t.creationDate = rd.nextDay AND t.userid = rd.userid)
SELECT t.*
FROM recur_date t
WHERE t.level = @numDays
ORDER BY t.userid
Joe Celko는 Smarties용 SQL(실행 및 시퀀스라고 함)에 이에 대한 전체 장을 가지고 있습니다.집에 그 책이 없어서 출근하면...제가 대답해 드릴게요.(데이터베이스 내역 테이블을 dbo라고 합니다.사용자 기록 및 일 수는 @Days)입니다.
SQL Team의 운영 관련 블로그에서 얻은 또 다른 정보
또 다른 아이디어는 다음과 같이 분할된 ROW_NUMBER와 함께 CTE를 사용하는 것입니다.
WITH Runs
AS
(SELECT UserID
, CreationDate
, ROW_NUMBER() OVER(PARTITION BY UserId
ORDER BY CreationDate)
- ROW_NUMBER() OVER(PARTITION BY UserId, NoBreak
ORDER BY CreationDate) AS RunNumber
FROM
(SELECT UH.UserID
, UH.CreationDate
, ISNULL((SELECT TOP 1 1
FROM dbo.UserHistory AS Prior
WHERE Prior.UserId = UH.UserId
AND Prior.CreationDate
BETWEEN DATEADD(dd, DATEDIFF(dd, 0, UH.CreationDate), -1)
AND DATEADD(dd, DATEDIFF(dd, 0, UH.CreationDate), 0)), 0) AS NoBreak
FROM dbo.UserHistory AS UH) AS Consecutive
)
SELECT UserID, MIN(CreationDate) AS RunStart, MAX(CreationDate) AS RunEnd
FROM Runs
GROUP BY UserID, RunNumber
HAVING DATEDIFF(dd, MIN(CreationDate), MAX(CreationDate)) >= @Days
위의 내용은 필요한 것보다 훨씬 더 어려울 수 있지만, 날짜가 아닌 "실행"에 대한 다른 정의가 있을 때 뇌 간지럼으로 남겨집니다.
몇 가지 SQL Server 2012 옵션(아래 N=100 포함).
;WITH T(UserID, NRowsPrevious)
AS (SELECT UserID,
DATEDIFF(DAY,
LAG(CreationDate, 100)
OVER
(PARTITION BY UserID
ORDER BY CreationDate),
CreationDate)
FROM UserHistory)
SELECT DISTINCT UserID
FROM T
WHERE NRowsPrevious = 100
비록 내 샘플 데이터로 다음이 더 효율적으로 해결되었지만,
;WITH U
AS (SELECT DISTINCT UserId
FROM UserHistory) /*Ideally replace with Users table*/
SELECT UserId
FROM U
CROSS APPLY (SELECT TOP 1 *
FROM (SELECT
DATEDIFF(DAY,
LAG(CreationDate, 100)
OVER
(ORDER BY CreationDate),
CreationDate)
FROM UserHistory UH
WHERE U.UserId = UH.UserID) T(NRowsPrevious)
WHERE NRowsPrevious = 100) O
둘 다 사용자당 하루에 최대 하나의 레코드가 있다는 질문에 명시된 제약 조건에 의존합니다.
이것이 당신에게 그렇게 중요하다면, 이 이벤트를 소스하고 이 정보를 제공하기 위해 표를 운전하세요.그 미친 질문들로 기계를 죽일 필요는 없습니다.
이런 거?
select distinct userid
from table t1, table t2
where t1.UserId = t2.UserId
AND trunc(t1.CreationDate) = trunc(t2.CreationDate) + n
AND (
select count(*)
from table t3
where t1.UserId = t3.UserId
and CreationDate between trunc(t1.CreationDate) and trunc(t1.CreationDate)+n
) = n
저는 간단한 수학 속성을 사용하여 누가 사이트에 연속적으로 접속했는지 확인했습니다.이 속성은 처음 액세스한 시간과 마지막 액세스 시간 사이의 날짜 차이가 액세스 테이블 로그의 레코드 수와 같아야 한다는 것입니다.
다음은 Oracle DB에서 테스트한 SQL 스크립트입니다(다른 DB에서도 작동해야 함).
-- show basic understand of the math properties
select ceil(max (creation_date) - min (creation_date))
max_min_days_diff,
count ( * ) real_day_count
from user_access_log
group by user_id;
-- select all users that have consecutively accessed the site
select user_id
from user_access_log
group by user_id
having ceil(max (creation_date) - min (creation_date))
/ count ( * ) = 1;
-- get the count of all users that have consecutively accessed the site
select count(user_id) user_count
from user_access_log
group by user_id
having ceil(max (creation_date) - min (creation_date))
/ count ( * ) = 1;
테이블 준비 스크립트:
-- create table
create table user_access_log (id number, user_id number, creation_date date);
-- insert seed data
insert into user_access_log (id, user_id, creation_date)
values (1, 12, sysdate);
insert into user_access_log (id, user_id, creation_date)
values (2, 12, sysdate + 1);
insert into user_access_log (id, user_id, creation_date)
values (3, 12, sysdate + 2);
insert into user_access_log (id, user_id, creation_date)
values (4, 16, sysdate);
insert into user_access_log (id, user_id, creation_date)
values (5, 16, sysdate + 1);
insert into user_access_log (id, user_id, creation_date)
values (6, 16, sysdate + 5);
declare @startdate as datetime, @days as int
set @startdate = cast('11 Jan 2009' as datetime) -- The startdate
set @days = 5 -- The number of consecutive days
SELECT userid
,count(1) as [Number of Consecutive Days]
FROM UserHistory
WHERE creationdate >= @startdate
AND creationdate < dateadd(dd, @days, cast(convert(char(11), @startdate, 113) as datetime))
GROUP BY userid
HAVING count(1) >= @days
cast(convert(char(11), @startdate, 113) as datetime)날짜의 시간 부분을 제거하여 자정에 시작합니다.
나는 또한 가정할 것입니다.creationdate그리고.userid열이 인덱스됩니다.
저는 이것이 모든 사용자와 사용자의 총 연속 일수를 알려주지 않을 것이라는 것을 깨달았습니다.그러나 어떤 사용자가 선택한 날짜로부터 지정된 날짜에 방문했는지 알 수 있습니다.
수정된 솔루션:
declare @days as int
set @days = 30
select t1.userid
from UserHistory t1
where (select count(1)
from UserHistory t3
where t3.userid = t1.userid
and t3.creationdate >= DATEADD(dd, DATEDIFF(dd, 0, t1.creationdate), 0)
and t3.creationdate < DATEADD(dd, DATEDIFF(dd, 0, t1.creationdate) + @days, 0)
group by t3.userid
) >= @days
group by t1.userid
확인해보니 모든 사용자와 모든 날짜가 조회됩니다.스펜서의 첫 번째(농담?) 솔루션을 기반으로 하지만, 내 솔루션은 작동합니다.
업데이트: 두 번째 솔루션에서 날짜 처리가 개선되었습니다.
이렇게 하면 원하는 대로 할 수 있지만 효율성을 테스트할 데이터가 부족합니다.Convert/FLOOD는 날짜 시간 필드에서 시간 부분을 제거하는 것입니다.SQL Server 2008을 사용하는 경우 CAST(x)를 사용할 수 있습니다.작성 날짜 AS DATE).
@범위를 INT로 선언SET @범위 = 10 DISTINCT UserId, Convert(DATTIME, FLOOR(변환), a를 선택합니다.작성일)FROM tbl사용자 로그인존재하는 위치(선택 1)FROM tbl사용자 로그인 b여기서 a.userId = b.userIdAND(카운트 선택(DISCTINCT(변환(DATETIME, FLOOR(변환(FLOAT, 생성일)))FROM tblUserLoginc여기서 c.userid = b.useridAND 컨버터(데이터 타임, 플로어(컨버터(플로트, c)).생성 날짜)) 사이의 변환(DATTIME, FLOOR(변환(플로트, a) 사이의 변환.CreationDate(생성일))) 및 CONVERT(DATIME, FLOOR(변환(FLOAT, a).작성일))+@범위-1) = @범위)
생성 스크립트
테이블 [dbo]을 만듭니다.[tblUserLogin]([Id] [int] ID(1,1) Null이 아닙니다.[UserId] [int] NULL,[CreationDate] [datetime] NULL) ON [기본]
스펜서가 거의 할 뻔했지만, 작동 코드는 이것이어야 합니다.
SELECT DISTINCT UserId
FROM History h1
WHERE (
SELECT COUNT(*)
FROM History
WHERE UserId = h1.UserId AND CreationDate BETWEEN h1.CreationDate AND DATEADD(d, @n-1, h1.CreationDate)
) >= @n
MySQLish는 바로 제 머리 속에 있습니다.
SELECT start.UserId
FROM UserHistory AS start
LEFT OUTER JOIN UserHistory AS pre_start ON pre_start.UserId=start.UserId
AND DATE(pre_start.CreationDate)=DATE_SUB(DATE(start.CreationDate), INTERVAL 1 DAY)
LEFT OUTER JOIN UserHistory AS subsequent ON subsequent.UserId=start.UserId
AND DATE(subsequent.CreationDate)<=DATE_ADD(DATE(start.CreationDate), INTERVAL 30 DAY)
WHERE pre_start.Id IS NULL
GROUP BY start.Id
HAVING COUNT(subsequent.Id)=30
테스트되지 않았고, MSSQL에 대한 변환이 거의 확실히 필요하지만, 저는 그것이 아이디어를 준다고 생각합니다.
Talile 테이블을 사용하는 것은 어떻습니까?그것은 더 알고리즘적인 접근법을 따르며 실행 계획은 간단합니다.테이블을 스캔할 1부터 'MaxDaysBehind'까지의 숫자로 talileTable을 채웁니다(예: 90은 3개월 뒤를 찾습니다).
declare @ContinousDays int
set @ContinousDays = 30 -- select those that have 30 consecutive days
create table #tallyTable (Tally int)
insert into #tallyTable values (1)
...
insert into #tallyTable values (90) -- insert numbers for as many days behind as you want to scan
select [UserId],count(*),t.Tally from HistoryTable
join #tallyTable as t on t.Tally>0
where [CreationDate]> getdate()-@ContinousDays-t.Tally and
[CreationDate]<getdate()-t.Tally
group by [UserId],t.Tally
having count(*)>=@ContinousDays
delete #tallyTable
빌의 질문을 조금 조정하고 있습니다.그룹화하기 전에 날짜를 잘라야 하루에 하나의 로그인만 카운트할 수 있습니다...
SELECT UserId from History
WHERE CreationDate > ( now() - n )
GROUP BY UserId,
DATEADD(dd, DATEDIFF(dd, 0, CreationDate), 0) AS TruncatedCreationDate
HAVING COUNT(TruncatedCreationDate) >= n
DATEADD(dd, DATEIFF(dd, 0, CreationDate, 101)를 convert(char(10), CreationDate, 101).
@ID는 가능합니다. 저는 아까 날짜 부분을 사용하려고 했지만 구문을 찾기가 너무 귀찮아서 대신 변환을 사용하기로 했습니다.저는 그것이 상당한 영향을 미쳤는지 모르겠습니다. 감사합니다! 이제 알았습니다.
다음과 같은 스키마를 가정합니다.
create table dba.visits
(
id integer not null,
user_id integer not null,
creation_date date not null
);
이렇게 하면 날짜 시퀀스에서 간격이 있는 연속 범위가 추출됩니다.
select l.creation_date as start_d, -- Get first date in contiguous range
(
select min(a.creation_date ) as creation_date
from "DBA"."visits" a
left outer join "DBA"."visits" b on
a.creation_date = dateadd(day, -1, b.creation_date ) and
a.user_id = b.user_id
where b.creation_date is null and
a.creation_date >= l.creation_date and
a.user_id = l.user_id
) as end_d -- Get last date in contiguous range
from "DBA"."visits" l
left outer join "DBA"."visits" r on
r.creation_date = dateadd(day, -1, l.creation_date ) and
r.user_id = l.user_id
where r.creation_date is null
언급URL : https://stackoverflow.com/questions/1176011/sql-to-determine-minimum-sequential-days-of-access
'source' 카테고리의 다른 글
| Sublime Text 2에서 Ctrl+D를 여러 개 선택할 때 일치를 건너뛸 수 있는 방법은 무엇입니까? (0) | 2023.05.22 |
|---|---|
| 바우어의 기본 구성 요소 폴더를 변경하는 방법은 무엇입니까? (0) | 2023.05.22 |
| 데이터 테이블 정렬 (0) | 2023.05.22 |
| Eclipse에서 스페이스 바 키 누름이 자동 완성을 트리거하지 않도록 합니다. (0) | 2023.05.22 |
| COUNT 집계에 "0"/"0" 결과를 포함시키는 방법은 무엇입니까? (0) | 2023.05.22 |