Database SQL uses lag and over functions and like use tips

1.

Sample 1:

Lag () is the previous row of records in the current order. The combination of over is the grouping statistical data.
The Lag () function is the data that goes to the field on the N row.

Sql> select * from X;

A
----------
1
2
3
5

Sql> Select a as snaped,
Lag (A, 2) over (ORDER by a) as Snapst
from X;

snaped Snapst
---------- ----------
1
2
3 1
5 2

Sql>

Sample 2:

Sql> select * from test;

QTY
----------
1
2
3
4

sql> Select SUM (qty) from test;

SUM (QTY)
----------
10

sql> Select SUM (qty) over (Order by qty) from test;

SUM (QTY) over (Orderbyqty)
------------------------
1
3
6
10

2.sql wildcard characters

Wildcard Description Example
% any string that contains 0 or more characters. The WHERE title like '%computer% ' will find all titles that contain the word computer anywhere in the title.
_ (underline) any single character. WHERE au_fname like ' _ean ' will look for all 4-letter names (Dean, Sean, etc.) ending in EAN.
[] Specifies any single character in the range ([A-f]) or collection ([abcdef]). WHERE au_lname like ' [C-p]arsen ' will find the last name of the author ending with Arsen and starting with any single character between C and P, for example, Carsen, Larsen, Karsen, and so on.

[^] Any single character that does not belong to the specified range ([a-f]) or set ([abcdef]). WHERE au_lname like ' de[^l]% ' will look for the last names of all authors that begin with the de and whose letters are not followed by L.

Database SQL uses lag and over functions and like use tips