Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

New additional functions similar LIST [CORE2134] #2565

Closed
firebird-automations opened this issue Oct 20, 2008 · 13 comments
Closed

New additional functions similar LIST [CORE2134] #2565

firebird-automations opened this issue Oct 20, 2008 · 13 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Igor Lobov (ivl)

Votes: 2

Create additional functions that are similar LIST, but returns only FIRST, LAST or ANY value from the list.

@firebird-automations
Copy link
Collaborator Author

Modified by: Igor Lobov (ivl)

Component: Engine [ 10000 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Bjoern Reimer (bnreimer)

Wouldn't MAX and MIN do the job?

@firebird-automations
Copy link
Collaborator Author

Commented by: Igor Lobov (ivl)

No. I think MAX and MIN is not applicable in lots cases.

For example:

Table
FIELD1, FIELD2, FIELD3

6                 2                   1
4                 2                   2
5                 2                   3 

SELECT FIELD2, COUNT(FIELD2), MIN(FIELD3), FIRST(FIELD3)
GROUP BY FIELD2
ORDER BY FIELD1

In this SELECT the functions MIN and FIRST will return different value.

The result of MIN will be "1", but the result of FIRST will be "2"

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Your desire will be achievable when full support of window functions is present. Something like:
select
field2,
count(field2),
min(field3)
min(field3) over (
partition by field2
order by field1
range between unbounded preceding and unbounded following
) first
from table;

@firebird-automations
Copy link
Collaborator Author

Commented by: Igor Lobov (ivl)

Yes, this good way is fit for me.
I hope it will be realized in nearest future.

But ORACLE SQL that includes window functions contains FIRST_VALUE() and LAST_VALUE(), which return the first and last values in a window.

@firebird-automations
Copy link
Collaborator Author

Commented by: Smirnoff Serg (wildsery)

select field2, cast(substring(min(lpad(field1, 10, '0') || field3) from 11) as int)
from Table
group by 1

@firebird-automations
Copy link
Collaborator Author

Commented by: Igor Lobov (ivl)

May be
select field2, cast(substring(min(lpad(cast(field1 as varchar(10)), 10, '0') || field3) from 11) as int)
^^^^^^^^^^^^^^^^^^^^^^^^
from Table
group by 1

But if I have more than one group field I obtain very long expression.
I think the realization of FIRST would be more short and universal solution of this problem.

@firebird-automations
Copy link
Collaborator Author

Commented by: Smirnoff Serg (wildsery)

1. There's no need to cast int as varchar. Moreover, no need external cast.
2. Ok, how about another solutions:

select t1.field2, (select first 1 field3 from Table where field2 = t1.field2 order by field1)
from Table t1
group by 1

execute block returns (field2 int, field3 int) as
declare variable cur_field2 int = -1;
begin
for select field2, field3 from Table order by 1, 2 into field2, field3
do
if (field2 != cur_field2) then
begin
suspend; cur_field2 = field2;
end
end

@firebird-automations
Copy link
Collaborator Author

Commented by: Igor Lobov (ivl)

I think that a lot of different solutions exist in the world.
As for me exist two very desired criteries:
1. Performance
2. Simplicity

I created the simple database with a single table
CREATE TABLE TEST_TABLE (
FIELD1 INTEGER,
FIELD2 INTEGER,
FIELD3 INTEGER
);

I inserted 1000000 records into the table.
And I executed some queries:

Query 1

SELECT FIELD2, MIN(FIELD3) FROM TEST_TABLE
GROUP BY FIELD2

Execute time = 2s 797ms

Query 2

SELECT FIELD2, LIST(FIELD3) FROM TEST_TABLE
GROUP BY FIELD2

Query 3

Execute time = 3s 359ms

SELECT FIELD2, CAST(SUBSTRING(MIN(LPAD(FIELD1, 10, '0') || FIELD3) FROM 11) AS INT) FROM TEST_TABLE GROUP BY 1

Execute time = 3s 422ms

Query 4

SELECT T1.FIELD2, (SELECT FIRST 1 FIELD3 FROM TEST_TABLE WHERE FIELD2 = T1.FIELD2 ORDER BY FIELD1) FROM TEST_TABLE T1
GROUP BY 1

Execute time = 4m 8s 890ms

Query 5

EXECUTE BLOCK RETURNS (FIELD2 INT, FIELD3 INT) AS
DECLARE VARIABLE CUR_FIELD2 INT = -1;
BEGIN
FOR SELECT FIELD2, FIELD3 FROM TEST_TABLE ORDER BY 1, FIELD1, 3 INTO FIELD2, FIELD3
DO
IF (FIELD2 != CUR_FIELD2) THEN
BEGIN
SUSPEND; CUR_FIELD2 = FIELD2;
END
END

Execute time = 3s 500ms

First and second query were written as a sample of performance.
Query 3 has good performance but it is not universal.
The performance of Query 4 is bad very much.
Query 5 was changed a little bit (because yours was mistaken). The performance of the query is good, but it is not universal too.

I think that built-in function could be the universal and high-speed solution.

Firebird 2.1.1 for Linux AMD64 under Fedora 9 x86_64 was used for my experiment.

@firebird-automations
Copy link
Collaborator Author

Commented by: Smirnoff Serg (wildsery)

> The performance of Query 4 is bad very much.

CREATE INDEX IDX1 ON TEST_TABLE (FIELD2, FIELD1);

@firebird-automations
Copy link
Collaborator Author

Commented by: Igor Lobov (ivl)

You can not add index to every field.
That is not very rational solution.

@dyemanov
Copy link
Member

We have FIRST_VALUE and LAST_VALUE (as well as NTH_VALUE) as window functions (since FB3, IIRC), and we have ANY_VALUE added as an aggregate function to FB6. Is it enough to have this ticket closed?

@mrotteveel
Copy link
Member

@dyemanov I think it can.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants