How to write a query in sql which does "=COUNTIF(B2:B4,">"&B2)" of excel/shee-CodePudding

I am trying to do "=COUNTIF(B1:B3,">"&B1)" in sql, it counts the number of elements in a column, from B1 row to B3, which are greater then B1. We are doing this for every 3 rows.

Suppose we have B column

532.02

667.96

588.1

579.35

623.98

621.29

Now we i need to calculate the number of elements which are greater then elements B(i) for every three rows.

532.02 -> 2 (=COUNTIF(B1:B3,">"&B1) (Number of elements from B1 to B3 which are greater then B1)

667.96 -> 0 (=COUNTIF(B2:B3,">"&B2) (Number of elements from B2 to B3 which are greater then B2)

588.1 -> 0 (=COUNTIF(B3:B3,">"&B3)(Number of elements from B3 to B3 which are greater then B3)

579.35 -> 2 (=COUNTIF(B4:B6,">"&B4) (Number of elements from B4 to B6 which are greater then B4)

623.98 -> 0 (=COUNTIF(B5:B6,">"&B5) (Number of elements from B5 to B6 which are greater then B5)

621.29 -> 0 (=COUNTIF(B6:B6,">"&B6) (Number of elements from B6 to B6 which are greater then B6)

So in sheets/excel, we use this (=COUNTIF(B5:B6,">"&B5), but how write query in sql on the basis of this?

CodePudding user response：

First of all if you want to have different behaviour depending on the position of the record (the groups of 3) then you have to have some way of showing that position, SQL guaranteed to be in a specific order unless it is specified. You would likely need to add a unique ID for each line that increments. For example:

CREATE TABLE MY_TABLE
(
  ROW_N NUMBER,
  AMMOUNT NUMBER
);
INSERT INTO MY_TABLE VALUES (1,532.02);
INSERT INTO MY_TABLE VALUES (2,667.96);
INSERT INTO MY_TABLE VALUES (3,588.1);
INSERT INTO MY_TABLE VALUES (4,579.35);
INSERT INTO MY_TABLE VALUES (5,623.98);
INSERT INTO MY_TABLE VALUES (6,621.29);

Then you can use a modulo function to change behaviour depending on whether the line you are working with has a reference that is exactly divisible by 3, a remainder of one, or a remainder of 2.

In order to compare values between different records on the same table you would likely need to use a self join. In this instance you are sometimes doing this twice, so you need 2 self joins.

The way i implimented it was to use a case statement, which is fine for doing a couple of comparisons, but isnt really scalable.

I used unions at the end just to get the data back into the original format.

WITH calcs
     AS (SELECT a.row_n   AS rown_a,
                a.ammount AS ammount_a,
                0         AS gtr_a,
                b.row_n   AS rown_b,
                b.ammount AS ammount_b,
                CASE
                  WHEN b.ammount < a.ammount THEN 1
                  ELSE 0
                END       AS gtr_b,
                c.row_n   AS rown_c,
                c.ammount AS ammount_c,
                CASE
                  WHEN c.ammount > a.ammount
                       AND c.ammount < b.ammount THEN 1
                  WHEN c.ammount < a.ammount
                       AND c.ammount > b.ammount THEN 1
                  WHEN c.ammount < a.ammount
                       AND c.ammount < b.ammount THEN 2
                  ELSE 0
                END       AS gtr_c
         FROM   my_table a,
                my_table b,
                my_table c
         WHERE  MOD(a.row_n, 3) = 0
                AND b.row_n = a.row_n - 1
                AND c.row_n = a.row_n - 2)
SELECT *
FROM   (SELECT rown_a,
               ammount_a,
               gtr_a
        FROM   calcs
        UNION
        SELECT rown_b,
               ammount_b,
               gtr_b
        FROM   calcs
        UNION
        SELECT rown_c,
               ammount_c,
               gtr_c
        FROM   calcs) x
ORDER  BY 1

This query is a bit of a mess, but what you are trying to do in SQL is a bit weird IMHO

CodePudding user response：

You could of use the CELL function with a new column that has the intervals of 3 (3,6,9,etc).

Then, you can build the formula to get the value of the interval column and proceed with the formula you have.

So, the formula to build would be like:

(=COUNTIF(B5:"B"&=CELL("contents", H33),">"&B5)

In this case, H33 has the interval.