Обсуждение: [SQL] Issues with lag command

Поиск
Список
Период
Сортировка

[SQL] Issues with lag command

От
Mohamed DIA
Дата:
Hello
I have a test table with the following structure (2 columns: ID and time_id )and data

ID, time_id

1;"2015-01-01"
2;""
3;""
4;"2015-01-02"
5;""
6;""
7;""
8;"2015-01-03"
9;""
10;""
11;""
12;""
13;"2015-01-05"
14;""
15;""
16;""

I'd like to update line 2 and 3 with the date in record 1 (2015-01-01)
Update line 5,6 and 7 with the date in record 4 (2015-01-02) and so on

The general logic is that anytime we find a record with a time_id null, we would like to update it with the previous time_id that is not null.
I use the LAG function and the below code



CREATE OR REPLACE FUNCTION public.update_test_dates()
  RETURNS SETOF test AS
$BODY$
DECLARE

    r test%rowtype;
BEGIN
    FOR r IN SELECT * FROM test  order by id
  
   
    LOOP
        -- can do some processing here
        if r.time_id is  null
        then
        update test set time_id= (select lag(time_id) OVER (ORDER BY id) from test where id=r.id) where id=r.id;
        end if;
       
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$BODY$
  LANGUAGE plpgsql;

  select * from update_test_dates();



However, it does not work. Postgres update all rows with a NULL value
Any one can tell me what needs to be changed in my procedure in order to fix the issue?

Regards

Re: [SQL] Issues with lag command

От
Steve Midgley
Дата:
On Fri, Jul 28, 2017 at 10:48 AM, Mohamed DIA <macdia2002@gmail.com> wrote:
Hello
I have a test table with the following structure (2 columns: ID and time_id )and data

ID, time_id

1;"2015-01-01"
2;""
3;""
4;"2015-01-02"
5;""
6;""
7;""
8;"2015-01-03"
9;""
10;""
11;""
12;""
13;"2015-01-05"
14;""
15;""
16;""

I'd like to update line 2 and 3 with the date in record 1 (2015-01-01)
Update line 5,6 and 7 with the date in record 4 (2015-01-02) and so on

The general logic is that anytime we find a record with a time_id null, we would like to update it with the previous time_id that is not null.
I use the LAG function and the below code



CREATE OR REPLACE FUNCTION public.update_test_dates()
  RETURNS SETOF test AS
$BODY$
DECLARE

    r test%rowtype;
BEGIN
    FOR r IN SELECT * FROM test  order by id
  
   
    LOOP
        -- can do some processing here
        if r.time_id is  null
        then
        update test set time_id= (select lag(time_id) OVER (ORDER BY id) from test where id=r.id) where id=r.id;
        end if;
       
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$BODY$
  LANGUAGE plpgsql;

  select * from update_test_dates();



However, it does not work. Postgres update all rows with a NULL value
Any one can tell me what needs to be changed in my procedure in order to fix the issue?

Regards

I may be wrong about this, but doesn't lag need an offset value? So your statement should be `lag(time_id, 1)`?

Maybe lag defaults to offset 1 but if not, then it would seem your current statement is grabbing its own row's time_id, and you want to pull the time_id from the row offset by 1?

Steve


Re: [SQL] Issues with lag command

От
"David G. Johnston"
Дата:
On Fri, Jul 28, 2017 at 12:21 PM, Steve Midgley <science@misuse.org> wrote:

The general logic is that anytime we find a record with a time_id null, we would like to update it with the previous time_id that is not null.
I use the LAG function and the below code


​Lag can be made to work but only if you know that maximum lag that guarantees a non-null value is present.  If you cannot pick a reasonable number then you should write a custom aggregate function.  I believe I've seen posts (probably to -general) detailing this and you might find it in blog posts or the like as well.  I'm unable to provide a working example right now.

David J.
​

Re: [SQL] Issues with lag command

От
Igor Neyman
Дата:

Hello

I have a test table with the following structure (2 columns: ID and time_id )and data


ID, time_id

1;"2015-01-01"
2;""
3;""
4;"2015-01-02"
5;""
6;""
7;""
8;"2015-01-03"
9;""
10;""
11;""
12;""
13;"2015-01-05"
14;""
15;""
16;""

I'd like to update line 2 and 3 with the date in record 1 (2015-01-01)

Update line 5,6 and 7 with the date in record 4 (2015-01-02) and so on

How about simple SQL instead of PlSql:

 

UPDATE test T1 SET time_id = (SELECT T2.time_id FROM test T2 WHERE T2.id =

          (SELECT max(T3.id) FROM test T3 WHERE T3.id < T1.id AND T3.time_id IS NOT NULL)

)

   WHERE T1.time_id IS NULL;

 

Regards,

Igor Neyman

 

 

Re: [SQL] Issues with lag command

От
Harald Fuchs
Дата:
Igor Neyman <ineyman@perceptron.com> writes:

> Hello
> I have a test table with the following structure (2 columns: ID and time_id )and data
>
> ID, time_id
> 1;"2015-01-01"
> 2;""
> 3;""
> 4;"2015-01-02"
> 5;""
> 6;""
> 7;""
> 8;"2015-01-03"
> 9;""
> 10;""
> 11;""
> 12;""
> 13;"2015-01-05"
> 14;""
> 15;""
> 16;""
> I'd like to update line 2 and 3 with the date in record 1 (2015-01-01)
> Update line 5,6 and 7 with the date in record 4 (2015-01-02) and so on
> How about simple SQL instead of PlSql:
>
> UPDATE test T1 SET time_id = (SELECT T2.time_id FROM test T2 WHERE T2.id =
>           (SELECT max(T3.id) FROM test T3 WHERE T3.id < T1.id AND T3.time_id IS NOT NULL)
> )
>    WHERE T1.time_id IS NULL;

You don't need that many table aliases:

UPDATE test
SET time_id =       ( SELECT T1.time_id        FROM test T1        WHERE T1.id < test.id          AND T1.time_id IS NOT
NULL         ORDER BY T1.id DESC          LIMIT 1      )
 
WHERE time_id IS NULL;