RaqForum 41 No.
335 View  • 
     ...

SQL, in each group modify the null value of a specified column as its neighboring value

 

The following PostgreSQL database table is ordered by date field and company field. Some values of column3 are NULL.

date

company

column3

2004-01-01

A

5

2004-01-01

B

NULL

2004-01-01

C

NULL

2004-01-02

A

NULL

2004-01-02

B

7

2004-01-02

C

NULL

2004-01-03

A

6

2004-01-03

B

7

2004-01-03

C

9

2004-01-04

A

NULL

2004-01-04

B

NULL

2004-01-04

C

NULL

Task: Handle records of same company in time order according to the specified rule: First, in each group, delete records until the first non-NULL column3 value appears; then modify each NULL column3 value to its directly previous column3 value until the next non-NULL column3 value appears. Repeat the modification until a new non-NULL column3 value appears, and so on.

date

company

column3

2004-01-01

A

5

2004-01-02

A

5

2004-01-02

B

7

2004-01-03

A

6

2004-01-03

B

7

2004-01-03

C

9

2004-01-04

A

6

2004-01-04

B

7

2004-01-04

C

9

Write the following SPL code:

1

=post1.query("select * from tb order by company,date")

2

=A1.run(if( company==company[-1], column3=ifn(column3,column3[-1])))

3

return A1.select(column3).sort(date)

A1: Query the database via JDBC and sort rows by company field and date field.

A2: Handle each record: for records of same group, do not modify the current column3 value if it is non-NULL, and modify it as the directly previous column3 value if it is NULL.

A3: Select records whose column3 value is non-NULL, sort them by date, and return the result.

Read How to Call a SPL Script in Java to find how to integrate SPL into a Java application.
Source:https://stackoverflow.com/questions/21839856/delete-null-values-until-first-value-is-not-null


 
 •  335 View  •