4 ms·

> esProc SPL is a JVM-based data computing class library Those who ignore the lessons of the past are doomed to reimplement them… poorly. Your first example i

by ttfkam 3y ago

> esProc SPL is a JVM-based data computing class library Those who ignore the lessons of the past are doomed to reimplement them… poorly. Your first example is what MATCH RECOGNIZE is for, no? https://modern-sql.com/feature/match_recognize https://modern-sql.com/feature/match_recognize Also, you could use CTEs to make your SQL example far more readable/maintainable. Dumping subselects everywhere doesn't really sell your case; it just makes your argument against SQL weaker right out of the gate. And on that note, why is it that folks always properly format and indent their code unless it's SQL? Yeah, if you write a statement with 150 characters in any language without formatting and indentation, it will look like crap. So why write your SQL with absolutely no efforts toward formatting and indentation unless you're intentionally trying to put your thumb on the scale? (On HN, you can preformat code by indenting four spaces.) The final bit that appears to be missed is that SQL DML, by design, doesn't specify HOW to retrieve the data, only WHAT data you want returned. This library is (a) intimately tied to the JVM, so immediately not language agnostic, and (b) straddling that line for HOW to get the data. In exchange for personal preferences on syntax and their aesthetics, you've lost declarative aspects of querying, no matter how cherry-picked the examples are. (C'mon! Be honest! How often does your example come up in real world development? I'm glad MATCH RECOGNIZE is making its way into the SQL toolbox, but it's not anything I'd be reaching for on a daily or even monthly basis.)

followSPL 3y ago
Thank you for your attention, there are many more examples like this 》example 1:List the last login interval for each user 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.
- ttfkam 3y ago
  > 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.
  - followSPL 3y ago
    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.
  - followSPL 3y ago
    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.

by ttfkam 3y ago

followSPL 3y ago

Thank you for your attention, there are many more examples like this 》example 1:List the last login interval for each user 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.

ttfkam 3y ago
> 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.
- followSPL 3y ago
  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.
- followSPL 3y ago
  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.