SQL에서 날짜 간 평균 시간 계산

SQL에서 날짜 간 평균 시간 계산

MySQL을 사용하여 질문에 대답하는 방법을 찾고 있습니다.사용자가 N번째 프로젝트를 작성하는 간격은 평균 몇 개월입니까?

예상 결과:

| project count | Average # months |
| 1             | 0                | # On average, it took 0 months to create the first project (nothing to compare to)
| 2             | 12               | # On average, it takes a user 12 months to create their second project
| 3             | 3                | # On average, it takes a user 3 months to create their third project

MySQL 테이블은 사용자가 만든 프로젝트를 나타냅니다.표는 다음과 같이 요약할 수 있습니다.

| user_id | project created at |
|---------|--------------------|
| 1       | Jan 1, 2020 1:00 pm|
| 1       | Feb 2, 2020 3:45 am|
| 1       | Nov 6, 2020 0:01 am|
| 1       | Mar 4, 2021 5:01 pm|
|------------------------------|
| 2       | Another timestamp  |
| 2       | Another timestamp  |
| 2       | Another timestamp  |
| 2       | Another timestamp  |
| 2       | Another timestamp  |
| 2       | Another timestamp  |
|------------------------------|
| ...     | Another timestamp  |
| ...     | Another timestamp  |

1개의 프로젝트를 가지는 유저도 있고, 수백개의 프로젝트를 가지는 유저도 있습니다.

편집: 현재 구현

with
    paid_self_serve_projects_presentation as (
        select 
                `Paid Projects`.owner_email
            `Owner Email`, 
                row_number() over (partition by `Paid Projects`.owner_uuid order by created_at)
            `Project Count`,
                day(`Paid Projects`.created_at)
            `Created Day`,
                month(`Paid Projects`.created_at)
            `Created Month`,
                year(`Paid Projects`.created_at)
            `Created Year`,
                `Paid Projects`.created_at
            `Created`
        from self_service_paid_projects as `Paid Projects`
        order by `Paid Projects`.owner_uuid, `Paid Projects`.created_at
    )
    
select `Projects`.* from paid_self_serve_projects_presentation as `Projects`

을 사용법나는 생각하고 있다.row_number()를 작성일 및 합니다.lag()이전 프로젝트가 생성된 날짜를 얻으려면:

select rn, avg(datediff(created_at, lag_created_at)) avg_diff_days
from (
    select t.*,
        row_number() over(partition by user_id order by created_at) rn,
        lag(created_at, 1, created_at) over(partition by user_id order by created_at) lag_created_at
    from mytable t
) t
group by rn

이렇게 하면 평균 일수 차이를 알 수 있으며, 그 달이 더 정확합니다.정말 몇 달을 원한다면, 몇 달을 원한다면timestampdiff(month, lag_created_at, created_at)datediff()단, 、 단 、 수 、 수 、 함 、 함 、 - 、 - - 、 - - - - - - - - - - - - - - - 。

언급URL : https://stackoverflow.com/questions/64145457/calculating-average-time-between-dates-in-sql