Do this kind of query often?

  • Which stocks have risen for five consecutive days this month?
  • What is the median revenue per region?
  • What is the growth in the three months with the best performance?
  • List the students who are in Top 10 in every subject?
  • By what month will sales of each salesman reach 500000?

Obsessed with these problems?

  • Dozens or hundreds of lines of SQL, with layers of nesting. Hard to understand even by the writer later on.
  • It will take several steps to solve the problem, but cannot write stored procedure for the database.
  • SQL debugging is hard, and it can't be tracked step by step. The only way is to print out temporary results. The efficiency is too low.
  • The window function and exists seem to be easy to use, but the calculation result is different from the expectation.

Why is SQL not suitable for writing complex queries?

Data is order-less

  • It's difficult to realize order-related calculation, and the sequence number need to be calculated.

Incompletely set-oriented

  • Do not have explicit set, unable to keep set data, and force aggregation while grouping.

Simple definition of join

  • The join operation is very confusing and error prone when it involves many tables.

Do not advocate step by step

  • It does not directly support step-by-step calculation, and stored procedures cannot be used in many scenarios.

Free computing with esProc

Fast and free implementation of temporary computation with unified syntax
Desktop level tool, ready to use, simple environment configuration!

Agile syntax

Count the longest consecutively rising trading days for a stock.

SQL

1 SELECT max(continuousDays)-1 FROM
2 (SELECT count(*) continuousDays FROM
3 (SELECT SUM(changeSign) OVER ( ORDER BY tradeDate) unRiseDays FROM
4 ( SELECT tradeDate,
5 CASE WHEN closePrice>LAG(closePrice) OVER( ORDER BY tradeDate THEN 0 ELSE 1 END changeSign
6 FROM stock ))
7 GROUP BY unRiseDays)

SPL

A
1 =orcl.query("select * from stock order by tradingday")
2 =A1.group@i(closePrice

Convenient development environment

Install and use immediately, with perfect debugging function

Procedure-oriented computing

Reliable loop branch control

Example

Calculate the moving average of monthly sales (one month before and one month after).

  A
1 =Sales.sort(month)
2 =A1.derive(Amount{-1,1}.avg()):Moving average)

Find stocks that rise three days in a row.

  A
1 =Stock.sort(tradingday)
2 =A1.group(code)
3 =A2.select((a=0,~.pselect(a=if(price>price[-1],a+1,0):3))>0)
4 =A3.(code)
Algorithm
For a company's organization table, query the subordinate organizations of the specified branch and list the names of its superior organizations. Multiple levels are separated by commas.
Data
ID ORG_NAME PARENT_ID
1 Head Office 0
2 Beijing Branch Office 1
3 Shanghai Branch Office 1
4 Chengdu Branch Office 1
5 Beijing R&D Center 2
...... ...... ......
Code
  A B
1 =connect("db") /Connect to database
2 =A1.query("select * from Organization") /Query organization table
3 >A2.switch(PARENT_ID,A2:ID) /The foreign key Parent_ID is mapped to the record where the ID is located to realize self join
4 =A2.select@1(ORG_NAME=="Beijing Branch Office") /Select the record of Beijing Branch
5 =A2.new(ID,ORG_NAME,~.prior(PARENT_ID,A4) :PARENT) /Create a new table consisting of ID, department name, and parent. The parent is obtained by recursively searching the records under Beijing branch through prior function.
6 =A5.select(PARENT!=null) /Select the members whose parent exists, otherwise they are not subordinates of Beijing Branch.
7 =A6.run(PARENT=PARENT.(PARENT_ID.ORG_NAME).concat@c()) /concatenate all the parent names in the parent field, separated by commas.

Resource Link

Installation and free authorization