트랜잭션당 개별 행 데이터 타임 스탬프로 정의된 일정 기간 동안의 트랜잭션을 카운트하는 방법

트랜잭션당 개별 행 데이터 타임 스탬프로 정의된 일정 기간 동안의 트랜잭션을 카운트하는 방법

MySQL 구문에서 MariaDB 10.2.4를 사용하고 있으며 각 트랜잭션에 대해 각 고객의 행의 타임 스탬프(달력일이 아닌)를 기준으로 이전 24시간 동안 발생한 트랜잭션 수를 계산하려고 합니다.

열 정보:

• 행_숫자:실제로 존재하지 않는 것을 읽기 쉽도록 추가했습니다.
• order_no: 각 행에 대한 고유 값입니다.
• customer_id: 고객별로 고유하지만 테이블에서 반복됩니다.
• order_date: 카운트를 수행해야 하는 타임스탬프입니다.
• dollar_value: 컨텍스트에 대한 값이 있으며 십진수입니다.

여기 내가 원하는 출력이 무엇인지 보여주기 위해 수동으로 만든 표가 있습니다.입력은 출력(trans_count) 및 행_number 열을 제외하고 모두 동일한 열이 됩니다.

예:

• 3열 관심있는 시간대는 고객 아이디 1111171로 2022-02-11 22:53:50(order_date 란에 있는 값)~2022-02-10 22:53:50(order_date - interval 1 day) 입니다.4행과 6행이 일치하므로 3행의 출력은 3(3행 포함)이 됩니다.

• 4열 고객님 1111171님의 시간은 2022-02-110 06:49:36 ~ 2022-02-10 06:49:36 입니다.6,8,9,10행은 기간 내에 속하며 동일한 customer_id이므로 trans_count 값은 5입니다.

테이블

행_숫자	순서_아니오	customer_id	order_date	달러_가치	trans_count
1	8888883	1111100	2022-02-14 01:10:04	2256.0	1
2	8888837	1111100	2022-02-12 05:46:32	1457.2	1
3	8888812	1111171	2022-02-11 22:53:50	1757.2	3
4	8888887	1111171	2022-02-11 06:49:36	1350.2	5
5	8888804	1111100	2022-02-11 03:10:07	1853.6	1
6	8888866	1111171	2022-02-11 01:20:26	1053.0	4
7	8888833	1111181	2022-02-10 21:09:05	253.2	1
8	8888874	1111171	2022-02-10 18:06:55	1958.6	3
9	8888829	1111171	2022-02-10 10:11:59	1456.2	2
10	8888802	1111171	2022-02-10 09:55:31	956.6	1
11	8888835	1111100	2022-02-09 19:40:24	756.4	2
12	8888810	1111123	2022-02-09 01:34:56	3179.5	1
13	8888850	1111100	2022-02-08 20:00:20	629.0	1
14	8888821	1111171	2022-02-08 17:59:05	1249.45	2
15	8888809	1111171	2022-02-08 06:25:15	1250.0	1
16	8888837	1111147	2022-02-08 06:18:15	184.6	1
17	8888836	1111171	2022-02-07 12:01:47	88.28	1
18	8888808	1111147	2022-02-05 12:02:49	3008.7	3
19	8888890	1111147	2022-02-05 11:48:16	1543.31	2
20	8888805	1111147	2022-02-05 11:37:55	2617.4	1

1000 이상의 dollar_value만 계산하는 것과 같은 다른 필터도 적용할 생각이지만 카운트의 논리에 영향을 미치지는 않을 것으로 예상합니다.

저는 PySpark에서 이것을 할 수 있었지만, 같은 논리를 반복할 수 없었습니다.여기 제가 통합한 함수의 일부분이 있습니다.

'''

# Function to calculate number of seconds from number of days
days = lambda i: i * 86400

# Create window by casting timestamp to long (number of seconds) then defining the number of days you wish to review
w = (Window.partitionBy('customer_id').orderBy(F.col(date_column).cast('long')).rangeBetween(-days(monitor_length_days), 0))

# Add total value of transactions undertaken within time frame by customer_id
filteredDF = filteredDF.withColumn(output_name, F.count('customer_id').over(w))

'''

질문이 있으시면 말씀해주세요.

안타깝게도 MariaDB에서는 일정 기간 동안 창을 만들 수 없기 때문에 다른 접근 방식이 필요합니다.이것을 해결하는 한가지 방법은JOIN그 자리에 테이블이 있습니다.customer_id값은 같으며.order_date이전 24시간 이내에 있습니다그런 다음 조인된 테이블의 행을 세어 다음을 얻을 수 있습니다.trans_count값:

SELECT o.customer_id, o.order_date,
       COUNT(c.customer_id) AS trans_count
FROM orders o
JOIN (SELECT customer_id, order_date
     FROM orders
     ) c ON c.customer_id = o.customer_id AND c.order_date BETWEEN o.order_date - INTERVAL 1 DAY AND o.order_date
GROUP BY o.customer_id, o.order_date
ORDER BY o.order_date DESC

출력(샘플 데이터의 경우):

customer_id order_date              trans_count
1111100     2022-02-14 01:10:04     1
1111100     2022-02-12 05:46:32     1
1111171     2022-02-11 22:53:50     3
1111171     2022-02-11 06:49:36     5
1111100     2022-02-11 03:10:07     1
1111171     2022-02-11 01:20:26     4
1111181     2022-02-10 21:09:05     1
1111171     2022-02-10 18:06:55     3
1111171     2022-02-10 10:11:59     2
1111171     2022-02-10 09:55:31     1
1111100     2022-02-09 19:40:24     2
1111123     2022-02-09 01:34:56     1
1111100     2022-02-08 20:00:20     1
1111171     2022-02-08 17:59:05     2
1111171     2022-02-08 06:25:15     2
1111147     2022-02-08 06:18:15     1
1111171     2022-02-07 12:01:47     1
1111147     2022-02-05 12:02:49     3
1111147     2022-02-05 11:48:16     2
1111147     2022-02-05 11:37:55     1

dbfiddle 데모

15행의 경우 다음과 같은 사항을 참고합니다.trans_count그래야 한다217번 행에 24시간 이내에 선행 거래가 있기 때문입니다.

언급URL : https://stackoverflow.com/questions/71731245/how-to-count-transactions-over-a-period-of-time-defined-by-the-individual-row-da