Separation of business logic and data storage due to system design
Diversity of data sources
SQL difficulty in implementing complex computational logic
Count the days each person is on duty
| A | |
|---|---|
| 1 | =file(“/Users/test/duty.xlsx”).importxls@tx() |
| 2 | =A1.groups(name;count(name):count) |
Get the duty records of the first three days for each person per month
| A | |
|---|---|
| 1 | =file(“/Users/test/duty.xlsx”).importxls@tx() |
| 2 | =A1.groups(month(workday):mon,name;~.top(3):top3) |
List the number of countries that use Chinese, English and French as the official language respectively in order
| A | |
|---|---|
| 1 | =connect(“mysql”) |
| 2 | =A1.query@x("select * from world.countrylanguage where isofficial='T'") |
| 3 | [Chinese,English,French] |
| 4 | =A2.align@a(A3,Language) |
| 5 | =A4.new(A3(#):name,~.len():cnt) |
select max(continuousDays)-1 from (select count(*) continuousDays from (select sum(changeSign) over(order by tradeDate) unRiseDays from (select tradeDate, case when closePrice>lag(closePrice) over(order by tradeDate) then 0 else 1 end changeSign from stock) ) group by unRiseDays)
| A | |
|---|---|
| 1 | =stock.sort(tradeDate) |
| 2 | =0 |
| 3 | =A1.max(A2=if(closePrice>closePrice[-1],A2+1,0)) |
According to our natural way of thinking, we sort data by date (line1), compare closing price of each day with the previous one, add 1 if the price is higher and reset the count if it is lower, and finally get the largest count (line3)
