The union of field values in SQL is common, such as firstname+lastname and year (birthday). No matter how many fields an expression contains, they come from the same row. We call this intra-row calculation. Correspondingly, there are inter-row calculations. Examples include getting the difference between the result of the champion and the runner-up and calculation of the accumulative sales amount starting from January to the current month. To identify the champion and the runner-up requires data be ordered by results. To do an accumulative sum from a certain point to another point also requires that data be ordered. So we call them ordered-based calculations. An intra-row calculation takes care of values within a single record, while an inter-row calculation handles the difference between ordered records.

Reference a value in the previous/next record

The simplest and the most common order-based calculation is the reference of a value in the previous or next record when records are already sorted in a certain order. Below are three scenarios:

1.Calculate the growth rate of a stock per day (link relative ratio)

Sort records by date and reference the closing price of the previous day.

2. Calculate the average price of a stock within three days, which are the previous day, the current day and the next day (moving average)

Sort records by date, and reference the closing price of the previous day and that of the next day.

3. There are multiple stocks. Calculate the growth rate of each stock in each trading day (intra-group link relative ratio)

Group records by stocks, sort each group by date and reference the closing price of the previous day.

Now let’s look at how SQL handles this type of order-based calculation.

#sql #database #developer