Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / IBM DB2, WebSphere, IMS, U2 [игнор отключен] [закрыт для гостей] / KEEP (эдакая аналитическая функция) / 13 сообщений из 13, страница 1 из 1
14.12.2005, 14:15
    #33435700
gardenman
gardenman
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KEEP (эдакая аналитическая функция)
Функция позволяет "запомнить" значение поля предыдущей строки, и использовать его в следующей.

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
/* keep.cpp */
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <memory.h>

#include <sql.h>
#include <sqludf.h> /* for use in compiling User Defined Function */

void SQL_API_FN keepint(
                        SQLUDF_INTEGER *inval,		//Input parameter
						SQLUDF_INTEGER *outval,		//Output parameter
                        //Null indicators
						SQLUDF_NULLIND *inval_ind,
						SQLUDF_NULLIND *outval_ind,
                        //
						SQLUDF_TRAIL_ARGS_ALL
) {
	struct s {
		bool tag;
		int val;
	};
	s *pSave=(s*)(SQLUDF_SCRAT->data);
	if (!pSave->tag) {
		pSave->tag=true;
		pSave->val= (*inval);
		*outval= 0 ;
		return;
	}
	*outval=pSave->val;
	if ((*inval_ind)== 1 ) {
		pSave->val= 0 ;
	} else {
		pSave->val=*inval;	
	}
}

void SQL_API_FN keepsmallint(
                        SQLUDF_SMALLINT *inval,		//Input parameter
						SQLUDF_SMALLINT *outval,	//Output parameter
                        //Null indicators
						SQLUDF_NULLIND *inval_ind,
						SQLUDF_NULLIND *outval_ind,
                        //
						SQLUDF_TRAIL_ARGS_ALL
) {
    //По умолчанию размер scratchpad = 100  байт
	struct s {
		bool tag;
		SQLUDF_SMALLINT val;
	};
	s *pSave=(s*)(SQLUDF_SCRAT->data);
	if (!pSave->tag) {
		pSave->tag=true;
		pSave->val= (*inval);
		*outval= 0 ;
		return;
	}
	*outval=pSave->val;
	if ((*inval_ind)== 1 ) {
		pSave->val= 0 ;
	} else {
		pSave->val=*inval;	
	}
}

void SQL_API_FN keepdt(
                        SQLUDF_DATE *inval,		//Input parameter
						SQLUDF_DATE *outval,	//Output parameter
                        //Null indicators
						SQLUDF_NULLIND *inval_ind,
						SQLUDF_NULLIND *outval_ind,
                        //
						SQLUDF_TRAIL_ARGS_ALL
) {
    //По умолчанию размер scratchpad = 100  байт
	struct s {
		int tag;
		SQLUDF_NULLIND ind;
		char val[ 11 ];
	};
	s *pSave=(s*)(SQLUDF_SCRAT->data);
	if (!pSave->tag) {
		pSave->tag= 1 ;
		pSave->ind=(*inval_ind);
		memcpy(&pSave->val,inval, 11 );
		*outval_ind=- 1 ;
		return;
	}
	memcpy(outval,&pSave->val, 11 );
	*outval_ind=(pSave->ind);
	memcpy(pSave->val,inval, 11 );
	pSave->ind=(*inval);
}

void SQL_API_FN keeptm(
                        SQLUDF_TIME *inval,		//Input parameter
						SQLUDF_TIME *outval,	//Output parameter
                        //Null indicators
						SQLUDF_NULLIND *inval_ind,
						SQLUDF_NULLIND *outval_ind,
                        //
						SQLUDF_TRAIL_ARGS_ALL
) {
    //По умолчанию размер scratchpad = 100  байт
	struct s {
		int tag;
		SQLUDF_NULLIND ind;
		char val[ 9 ];
	};
	s *pSave=(s*)(SQLUDF_SCRAT->data);
	if (!pSave->tag) {
		pSave->tag= 1 ;
		pSave->ind=(*inval_ind);
		memcpy(&pSave->val,inval, 9 );
		*outval_ind=- 1 ;
		return;
	}
	memcpy(outval,&pSave->val, 9 );
	*outval_ind=(pSave->ind);
	memcpy(pSave->val,inval, 9 );
	pSave->ind=(*inval);
}

void SQL_API_FN keepts(
                        SQLUDF_STAMP *inval,		//Input parameter
						SQLUDF_STAMP *outval,	//Output parameter
                        //Null indicators
						SQLUDF_NULLIND *inval_ind,
						SQLUDF_NULLIND *outval_ind,
                        //
						SQLUDF_TRAIL_ARGS_ALL
) {
    //По умолчанию размер scratchpad = 100  байт
	struct s {
		int tag;
		SQLUDF_NULLIND ind;
		char val[ 27 ];
	};
	s *pSave=(s*)(SQLUDF_SCRAT->data);
	if (!pSave->tag) {
		pSave->tag= 1 ;
		pSave->ind=(*inval_ind);
		memcpy(&pSave->val,inval, 27 );
		*outval_ind=- 1 ;
		return;
	}
	memcpy(outval,&pSave->val, 27 );
	*outval_ind=(pSave->ind);
	memcpy(pSave->val,inval, 27 );
	pSave->ind=(*inval);	
}


void SQL_API_FN keepdouble(
                        SQLUDF_DOUBLE *inval,		//Input parameter
						SQLUDF_DOUBLE *outval,	//Output parameter
                        //Null indicators
						SQLUDF_NULLIND *inval_ind,
						SQLUDF_NULLIND *outval_ind,
                        //
						SQLUDF_TRAIL_ARGS_ALL
) {
    //По умолчанию размер scratchpad = 100  байт
	struct s {
		bool tag;
		double val;
	};
	s *pSave=(s*)(SQLUDF_SCRAT->data);
	if (!pSave->tag) {
		pSave->tag=true;
		pSave->val= (*inval);
		*outval= 0 ;
		return;
	}
	*outval=pSave->val;
	if ((*inval_ind)== 1 ) {
		pSave->val= 0 ;
	} else {
		pSave->val=*inval;	
	}
}

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
#keep.dep
LIBRARY keep
EXPORTS keepdt
EXPORTS keepts
EXPORTS keeptm
EXPORTS keepint
EXPORTS keepsmallint
EXPORTS keepdouble

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
#makefile
DBUSER=db2admin
DBNAME=test
DBPASSWORD=ibmdb2

TARGET=keep.dll
DEF=keep.def
LIBS=db2api.lib

CPPOBJS= keep.obj
CFLAGS= -Z7 -O2 -G5 -c -W2 -D_X86_= 1  -DWIN32 -GR -GX -IK:\SQLLIB\INCLUDE
LINKFLAGS= /DEBUG /DLL /LIBPATH:k:\sqllib\lib

.SUFFIXES:
.SUFFIXES: .obj .sqx .cpp

.cpp.obj:
	cl $(CFLAGS) $*.cpp

.sqx.obj:
	echo CONNECT TO $(DBNAME) USER $(DBUSER) USING '$(DBPASSWORD)' > $*.db2
	echo PREP $*.sqx BINDFILE >> $*.db2
	echo BIND $*.bnd >> $*.db2
    @if exist $*.log del $*.log
    d:\sqllib\bin\db2clpex db2 -z $*.log -vf $*.db2
    type $*.log
	cl $(CFLAGS) $*.cxx

$(TARGET): $(CPPOBJS) Makefile keep.def
	link $(LINKFLAGS) -out:$(TARGET) $(SQXOBJS) $(CPPOBJS) $(LIBS) -def:$(DEF)
#    copy $(TARGET) d:\sqllib\function

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
-- register
connect to test@

create function keep(par int) returns int
specific KEEPINT
external name 'keep!keepint'
language C
parameter style sql
deterministic
fenced
called on null input
no sql
no external action
scratchpad  64 
final call
no dbinfo
@

create function keep(par smallint) returns smallint
specific KEEPSMALLINT
external name 'keep!keepsmallint'
language C
parameter style sql
deterministic
fenced
called on null input
no sql
no external action
scratchpad  64 
final call
no dbinfo
@



create function keepdateiso(par date) returns date
specific KEEPDATEISO
external name 'keep!keepdt'
language C
parameter style sql
deterministic
fenced
called on null input
no sql
no external action
scratchpad  64 
final call
no dbinfo
@


create function keep(par date) returns date
specific KEEPDATE
language sql
deterministic
called on null input
no external action
return
	date(keepdateiso(par))
@



create function keep(par time) returns time
specific KEEPTIME
external name 'keep!keeptm'
language C
parameter style sql
deterministic
fenced
called on null input
no sql
no external action
scratchpad  64 
final call
no dbinfo
@

create function keep(par timestamp) returns timestamp
specific KEEPTIMESTAMP
external name 'keep!keepts'
language C
parameter style sql
deterministic
fenced
called on null input
no sql
no external action
scratchpad  64 
final call
no dbinfo
@

create function keep(par double) returns double
specific KEEPDOUBLE
external name 'keep!keepdouble'
language C
parameter style sql
deterministic
fenced
called on null input
no sql
no external action
scratchpad  64 
final call
no dbinfo
@

commit@

пример использования:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select
    tableid,
    keep(tableid),
    tableid+keep(tableid)
from
    syscat.tables
where
    TBSPACEID= 0 
    and	tableid> 0 
order
    by tableid@

Считает сумму всех tableid нарастающим итогом,
хотя она предназначена не для этого.
На самом деле она предназначена
только для хранения значения предыдущей строки .
Подобного результата добиться с помощью аналитических функций непросто.
Внимание! Чтобы получить сумму нарастающим итогом (или посчитать записи) следует использовать функцию counter, которую можно найти на этом же форуме.

Очень важна фраза ORDER BY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2005, 14:29
    #33435762
gardenman
gardenman
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KEEP (эдакая аналитическая функция)
Кому лень (или не получается) скомпилить - DLL прилагается.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2005, 15:12
    #33435941
Herr Developer
Herr Developer
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KEEP (эдакая аналитическая функция)
Зашибись. "C" рулит
Надо только почитать как собрать все это под Linux x86-64.

P.S.
"Нашел" у себя тут табличку, типа с полями CURR_VALUE,PREV_VALUE, сейчас получаеться можно все это "выбросить" (+код, который это "поддерживал") и просто использовать KEEP...

Cпасибо большое gardenman'у
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2005, 15:20
    #33435963
gardenman
gardenman
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KEEP (эдакая аналитическая функция)
Как это...
There is no any warranty...
То бишь be careful...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2005, 16:50
    #33436236
adolf
adolf
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KEEP (эдакая аналитическая функция)
2 gardemann

>Функция позволяет "запомнить" значение поля предыдущей строки , и >использовать его в следующей

что за ПРЕДЫДУЩАЯ строка такая?

Данное поле описано как GENERATED и мы говорим о строке с максим. значением генератора или же о строке с макс. временем вставки, которое лепится на триггере и хранится отдельно? Об чем речь?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2005, 16:54
    #33436251
adolf
adolf
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KEEP (эдакая аналитическая функция)
2 gardemann

>Функция позволяет "запомнить" значение поля предыдущей строки , и >использовать его в следующей

что за ПРЕДЫДУЩАЯ строка такая?

Данное поле описано как GENERATED и мы говорим о строке с максим. значением генератора или же о строке с макс. временем вставки, которое лепится на триггере и хранится отдельно? Об чем речь?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2005, 17:08
    #33436301
gardenman
gardenman
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KEEP (эдакая аналитическая функция)
предположим у вас есть такая таблица:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
create table test_keep (
	id int,
	cnt int
)@

insert into test_keep values 
( 1 , 10 ),
( 2 , 233 ),
( 3 , 4545 ),
( 5 , 44 ),
( 6 , 1212 )
@

select * from test_keep@

select id,cnt,keep(cnt),cnt-keep(cnt) from test_keep@
rollback@

результат:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
select * from test_keep

ID          CNT        
----------- -----------
           1            10 
           2           233 
           3          4545 
           5            44 
           6          1212 

   5  record(s) selected.


select id,cnt,keep(cnt),cnt-keep(cnt) from test_keep

ID          CNT          3             4           
----------- ----------- ----------- -----------
           1            10             0            10 
           2           233            10           223 
           3          4545           233          4312 
           5            44          4545        - 4501 
           6          1212            44          1168 

   5  record(s) selected.


rollback
DB20000I  The SQL command completed successfully.

понятно?
я беру значение CNT из текущей строки и отнимаю от него значение CNT предыдущей. (т.е. получаю разницу между предыдущей и следующей).
Такие вещи в простом SQL без курсоров не делаются.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2005, 17:47
    #33436434
adolf
adolf
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KEEP (эдакая аналитическая функция)
2 gardemann

я понял, что ф-ция keep НЕ требует упорядочиванию выборки.
Другими словами (в вашем примере запрос неупорядочен): допустим, для данного случая манагер бд предоставит выборку так

select * from test_keep

ID CNT
----------- -----------
6 1212
2 233
3 4545
5 44
1 10

keep(cnt)
----------
0
1212
233
4545
44

Это так?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2005, 18:09
    #33436487
gardenman
gardenman
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KEEP (эдакая аналитическая функция)
так.
Упорядочивать нужно в соответствии с бизнес-логикой.
WITH позволяет это делать внутри запроса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.12.2005, 19:51
    #33436658
Herr Developer
Herr Developer
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KEEP (эдакая аналитическая функция)
2 gardenman
Надо уже FAQ писать
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.12.2005, 05:30
    #33436992
golsa
golsa
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KEEP (эдакая аналитическая функция)
2 gardemann

Это делается и обычным SQL:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
create table test_keep (
	id int,
	cnt int
)@

insert into test_keep values 
( 1 , 10 ),
( 2 , 233 ),
( 3 , 4545 ),
( 5 , 44 ),
( 6 , 1212 )
@

select * from test_keep@

select a.id,a.cnt,coalesce(b.cnt, 0 ),cnt - coalesce(b.cnt, 0 ) 
from test_keep a
       left join test_keep b on b.id = a.id -  1  @   

rollback@
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.12.2005, 05:34
    #33436993
golsa
golsa
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KEEP (эдакая аналитическая функция)
Извините ошибочка вкралась:

select a.id,a.cnt,coalesce(b.cnt,0), a. cnt - coalesce(b.cnt,0)
from ...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.12.2005, 11:19
    #33437451
gardenman
gardenman
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
KEEP (эдакая аналитическая функция)
golsaИзвините ошибочка вкралась:

select a.id,a.cnt,coalesce(b.cnt,0), a. cnt - coalesce(b.cnt,0)
from ...

В чем смысл знаете?
А в том, что вся работа делается за один проход без лишнего I/O.
Т.е. смысл - производительность. Прикиньте с какой скоростью будет работать то что вы написали, если в результирующей табличке порядка 100000 записей.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / IBM DB2, WebSphere, IMS, U2 [игнор отключен] [закрыт для гостей] / KEEP (эдакая аналитическая функция) / 13 сообщений из 13, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]