Re:Show HN: It's been nearly 50 years. Is SQL outdated?
by ttfkam on Wednesday October 25, 2023 @10:32AM
(#38013213)
> Furthermore, simple grammar is only superficial, and the new algebra will actually bring performance improvements. For example, using ordered grouping instead of hash grouping, using merge instead of hash when associating, and so on.
Again, there appears to be disconnect with regard to SQL. SQL does not specify ordered vs hash grouping nor merge instead of hash. That is an engine implementation detail and only touched upon in DDL (schema declaration).
The query side of SQL (DML) does not specify index or grouping strategies. At all. One bit. By design.
Got a reference to a table on a different server—or even a different geographic location? Doesn't matter. The SQL query doesn't change. Local or remote, physical or virtual, reactive or static, the query language doesn't change.
Re:Show HN: It's been nearly 50 years. Is SQL outdated?
by followSPL on Thursday October 26, 2023 @12:15AM
(#38021530)
Thank you for your reply,It's a pleasure to discuss it with you.We are not only discussing the query side of SQL (DML), it is the essence of SQL, relational algebra, and the examples in the post are just for a more intuitive representation of this.
The efficiency of executing a complex SQL statement can usually only be achieved through the automatic optimization function of the database engine. As you said, SQL does not specify ordered vs hash grouping nor merge instance of hash That is an engine implementation detail and only touched upon in DDL (schema declaration). Even if more optimization is done in DML, the selection of the underlying algorithm cannot be changed, at most, it makes writing SPL more convenient for users.
Re:Show HN: It's been nearly 50 years. Is SQL outdated?
by followSPL on Friday October 27, 2023 @12:00AM
(#38034541)
Thank you for your reply,It's a pleasure to discuss it with you.We are not only discussing the query side of SQL (DML), it is the essence of SQL, relational algebra, and the examples in the post are just for a more intuitive representation of this. The efficiency of executing a complex SQL statement can usually only be achieved through the automatic optimization function of the database engine. As you said, SQL does not specify ordered vs hash grouping nor merge instance of hash That is an engine implementation detail and only touched upon in DDL (schema declaration). Even if more optimization is done in DML, the selection of the underlying algorithm cannot be changed, at most, it makes writing SQL more convenient for users.
SQL:
WITH TT AS (SELECT RANK() OVER(PARTITION BY uid ORDER BY logtime DESC) rk, T.* FROM t_loginT) SELECT uid,(SELECT TT. logtime FROM TT where TT.uid=TTT. uid and TT.rk=1) -(SELET TT. logtim FROM TT WHERE TT.uid=TTT. uid and TT.rk=2) interval FROM t_loginTTTT GROUP BY uid
SPL: =t_login.groups(uid;top(2,-logtime)).new(uid,#2(1).logtime-#2(2).logtime:interval)
》example 2:Calculate the moving average of sales for each month before and after each month
SQL:
WITH B AS (SELECT LAG(amount) OVER (ORDER BY smonth) f1, LEAD(amount) OVER (ORDER BY smonth) f2, A.* FROM orders A) SELECT smonth,amount, (NVL(f1,0)+NVL(f2,0)+amount)/(DECODE(f1,NULLl,0,1)+DECODE(f2,NULL,0,1)+1) moving_average FROM B
SPL: =orders.sort(smonth).derive(amount[-1,1].avg()):moving_average)
Complex SQL is not something that everyone encounters every day, and the financial industry often has more. And MATCH_ RECOGNIZE currently does not have the functionality of all databases, and we cannot require everyone to use such databases. Furthermore, simple grammar is only superficial, and the new algebra will actually bring performance improvements. For example, using ordered grouping instead of hash grouping, using merge instead of hash when associating, and so on.