SQL中地址更改的频率(以天数为单位)

Question

嗨，我想知道企业更改地址的频率。我有两张桌子，一张是交易地址，另一张是办公地址。复杂的部分是一个id将有几个序列号。我需要找出一个地址的创建日期和另一个地址创建日期之间的差异。

Trading address table

>0瓶道//tr>

ID	Create_date	Seq_no
1	2002年03月23日	1
1	2002年5月23日	2	12日落大道
2	2003年1月14日	1	76 moonrise ct

我想我解决了它。步骤是

1. 我做了一个并集，并创建了一个单独的列来查找实际并集的序列号。

2. 使用LEAD函数创建的单独列以显示日期。

3. 日期差异，以找出id的之间的实际差异

4. 对天数进行分类并计算id的的案例说明

   WITH BASE AS (
   SELECT ID,SEQ_NO,CREATE_DATE
   FROM TradingAddress
   UNION ALL
   SELECT ID,SEQ_NO,CREATE_DATE
   FROM OfficeAddress
   ),
   WORKINGS AS  (
   SELECT ID,CREATE_DATE,
   DENSE_RANK() OVER (PARTITION BY ID ORDER BY CREATE_DATE ASC) AS SNO,
   LEAD(CREATE_DATE) OVER (PARTITION BY ID ORDER BY CREATE_DATE) AS REF_DATE,
   DATEDIFF(DAY,CREATE_DATE,LEAD(CREATE_DATE) OVER (PARTITION BY ID ORDER BY CREATE_DATE)) AS DATE_DIFFERENCE
   FROM BASE
   ),
   WORKINGS_2 AS (
   SELECT *,
   CASE WHEN DATE_DIFFERENCE BETWEEN 1 AND 30 THEN '1-30 DAYS'
   WHEN DATE_DIFFERENCE BETWEEN 31 AND 60 THEN '31-60 DAYS'
   WHEN DATE_DIFFERENCE BETWEEN 61 AND 90 THEN '61-90 DAYS'
   WHEN DATE_DIFFERENCE BETWEEN 91 AND 120 THEN '91-120 DAYS'ELSE 'MORE THAN 120 DAYS' 
   END AS DIFFERENCE_DAYS
   FROM WORKINGS
   WHERE REF_DATE IS NOT NULL
   )
   SELECT DIFFERENCE_DAYS,COUNT(DIFFERENCE_DAYS) AS NUMBEROFIDS
   FROM WORKINGS_2
   GROUP BY DIFFERENCE_DAYS
   

你可以用这种方式

SELECT DATEDIFF(day,t1.create_date,t2.create_date) AS 'yourdats', Count (*) as ids FROM test1 t1 join test2 t2 on t1.id = t2.id GROUP BY DATEDIFF(day,t1.create_date,t2.create_date)