|
I want to number between the hashtags according to my new column Rownum here below. I can't solve it, anyone who knows how to do it? My table look like this: Tag Pos ------------------- # 10 (NULL) 20 (NULL) 30 (NULL) 40 # 50 (NULL) 60 (NULL) 70 # 80 (NULL) 90 # 100 (NULL) 110 (NULL) 120 (NULL) 130 (NULL) 140 I want to create one more field that counts up on each hashtag like this: Tag Pos Rownr --------------------------------- # 10 1 (NULL) 20 1 (NULL) 30 1 (NULL) 40 1 # 50 2 (NULL) 60 2 (NULL) 70 2 # 80 3 (NULL) 90 3 # 100 4 (NULL) 110 4 (NULL) 120 4 (NULL) 130 4 (NULL) 140 4 |
|
You could get this with a two Window Functions. create table Test(tag char(1), Pos integer not null primary KEY )
insert into Test
values ('#', 10), (null, 20), (null, 30), ('#', 40), (null, 50),
(null, 60), ('#', 70), (null, 80), (null, 90), ('#', 100);
select tag, pos
, sum(test) over (order by pos RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "Res"
from (
select *
, last_value(if tag is null then 0 else 1 endif) over (order by POS RANGE BETWEEN 1 PRECEDING AND current row) as test
from test
) tempResult
order by pos asc
I really don't know how fast this is when you have real data. HTH IMHO window functions are usually quite fast, at least way faster than self-joins that might be used to simulate them...
(13 Feb '20, 06:58)
Volker Barth
Replies hidden
When you have a large data set and you need to filter it you can't easily filter the raw data when you need the window function results based on the complete data set like above.
(13 Feb '20, 07:03)
Thomas Dueme...
Ah, you relate to that question How can I filter the results of a WINDOW function?? Well, as long as it is turned into a derived query, you can filter on that easily - whether the performance is great might vary but I guess it's not worse compared to other techniques where you have to compare rows of a table with each other...
(13 Feb '20, 07:22)
Volker Barth
|
|
For the results abobe you can use the if statment |
