...

Calculate the Average Interval of Projects for Each User

 
order-based calculation(16) transpose(10)

Problem description & analysis

We have a table PROJECTS in the database. Below is a part of the table:

UID

PD

1

3-Jan-2001

1

18-Feb-2001

1

22-Jul-2001

2

12-Sep-2000

2

20-Nov-2000

2

4-Apr-2001

…

…

UID contains user IDs. PD contains project creation date. We are trying to calculate the average interval (month) of different projects for each user. Below is part of the desired result:

pc

avg

1

0

2

4

3

4

4

2

…

…

Solution

Write the following script p1.dfx in esProc:

A

1

=connect("demo")

2

=A1.query@x("SELECT * FROM PROJECTS ORDER BY   UID,PD").group(UID).(~.(interval@m(PD[-1],PD))).run(~(1)=0)

3

>pm=A2.max(~.len())

4

=A2.(~.pad(0,pm))

5

=transpose(A4).new(#:pc,int(ceil(avg(~))):avg)

Explanation:

A1   Connect to the database named demo.

A2  Return the query result as a table sequence and close database connection when code is executed. Group the table sequence by UID, and for each group calculate the month difference of each project creation date and the previous project creation date. Assign 0 to the value of the first member in each group.

A3  Define variable pm (the maximum number of projects of each user) and calculate the number.

A4  Supply 0s to A2, a sequence of sequences, according to value of pm.

A5  Transpose rows to columns on A4 and calculate the averages.

Read How to Call an SPL SCript in Java to learn about the method of integrating the SPL script with a Java program.

Q & A Collection

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


order-based calculation(16) transpose(10)
 
 •  173 View  •