-2

I have a table as shown below:

CAR NAME    INSERT DATE
MERCEDES    2018-01-01
SEAT        2018-01-01
MERCEDES    2018-01-02
BMW         2018-01-02
MERCEDES    2018-01-03
MERCEDES    2018-01-04
MERCEDES    2018-01-05
BMW         2018-01-05
BMW         2018-01-06
SEAT        2018-01-07
BMW         2018-01-08
AUDI        2018-01-08
BMW         2018-01-09  
BMW         2018-01-10
NULL        2018-01-12
SEAT        2018-01-12
SEAT        2018-01-14
SEAT        2018-01-16
BMW         2018-01-17
NULL        2018-01-19 
MERCEDES    2018-01-21
MERCEDES    2018-01-22
MERCEDES    2018-01-23

I would like to know how many consecutive times the same CAR NAME was inserted into the table, when ordered by INSERT DATE, as well as the first and last INSERT DATE. For the purposes of this query, results of one consecutive CAR NAME should be ignored.

For example:

name  count  first     last
mercedes 3 2018-01-03 2018-01-05
bmw      2 2018-01-05 2018-01-06
bmw      2 2018-01-09 2018-01-10
seat     3 2018-01-12 2018-01-16
mercedes 3 2018-01-21 2018-01-23

I have a problem with implementing this, maybe someone could help.

This is for SQL Server. Unfortunately I have only two columns at my disposal.

5

Your question is not clearly formed, but considering your examples - Phil's comment was correct:

Your example only makes sense if there is another column that defines the order of the data (which is the order you have presented the data as in your question).

Unless you have an additional column with the order of rows - there is no solution to your problem.

Why? Because SQL is based on the theory of relations and in this concept the data itself have no order. So unless you provide an additional column with order of rows (most commonly this would be Id column with incrementing numbers), there would be no way of telling the order of data and thus - your question could not be answered. Without a column with order numbers if you perform SELECT from the database, the SQL officially does not make any guarantees that you will receive rows in the same order every time (and in many cases you won't).

Solution Add another column, e.g. Id as integer and have each row have an incrementing value like this:

Id    NAME         DATE
1   MERCEDES    2018-01-01
2   SEAT        2018-02-01
3   MERCEDES    2018-04-01
4   BMW         2018-01-01
5   MERCEDES    2018-01-01
6   MERCEDES    2018-01-05
7   MERCEDES    2018-01-09

I had much fun figuring out how you could actually query to get the results you wanted, but here it is (sorry I didn't put much time into formatting):

;WITH First (id, car, d, is_first, rn) AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM (
        SELECT
            id
            ,car
            ,d
            ,CASE WHEN ((LEAD(car,1) OVER (ORDER BY id) = car) AND (LAG(car,1) OVER (ORDER BY id) <> car OR LAG(car,1) OVER (ORDER BY id) IS NULL)) THEN 1 ELSE 0 END is_first
        FROM 
            dbo.cars
    ) t
    WHERE t.is_first = 1
),
Last (id, car, d, is_last, rn) AS
(
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM (
        SELECT
            id
            ,car
            ,d
            , CASE 
                WHEN (LEAD(car,1) OVER (ORDER BY id) <> car OR LEAD(car,1) OVER (ORDER BY id) IS NULL) AND (LAG(car,1) OVER (ORDER BY id) = car ) THEN 1 ELSE 0 END is_last
        FROM 
            dbo.cars
    ) t
    WHERE t.is_last = 1
)
SELECT
    c.car, COUNT(*) cnt, f.d min_date, l.d max_date
FROM
    First f
    LEFT JOIN Last l ON f.rn = l.rn
    LEFT JOIN cars c ON c.car = f.car AND c.id BETWEEN f.id AND l.id
GROUP BY 
    c.car, f.d, l.d, f.rn
ORDER BY 
    f.rn

The main idea was to find first and last items of ranges (using windowing function LAG and LEAD) and then pair first items with last items using ROW_NUMBER() as keys. And last but not least join these pairs with the original table again just to get COUNT(*)s. Et voila:

fun query

| improve this answer | |
  • 2
    Nice. You may want to add that the problem is in the category of "gaps-and-islands" problems. – ypercubeᵀᴹ Jun 30 '18 at 6:39
1

As explained in comments your question is not clear, but if you want, per name, the count, min and max, you can do:

SELECT name, COUNT(*), min(date), max(date) FROM atable GROUP BY name

Try it yourself: http://sqlfiddle.com/#!15/50fcb/5/0

It does not produce the output you show because your example is not clear/complete (example: you list a count of 3 for seat where you have 4 lines of it...)

| improve this answer | |
1

Old thread, but it's still an interesting problem.

As Błażej mentioned, we need a column which can be sorted, with no ties. Otherwise, the query will not know which record came first and will count things as a group in some unpredictable way.

Assuming we have this column (let's name it seq), the following query also works:

 WITH grouped_cars AS (
  SELECT name,
         date,
         (
           name,
           -- There is a subtraction below,
           -- don't be fooled by the formatting
           DENSE_RANK() OVER (ORDER BY seq) 
         - DENSE_RANK() OVER (PARTITION BY name ORDER BY seq)
         ) AS car_group
  FROM cars
  )
SELECT MIN(name), -- Could be 'ARBITRARY(name)' in Presto
       COUNT(1) AS count, 
       MIN(date) AS first,
       MAX(date) AS last
FROM grouped_cars
GROUP BY car_group
HAVING COUNT(1) > 1
ORDER BY MIN(date)

Here's a link to the SQLFiddle with the query: http://sqlfiddle.com/#!17/10304/5

The trick is really well explained in this question: Solving "Gaps and Islands" with row_number() and dense_rank()?

How it works?

The trick is that if you subtract two running sequences you get the same number for all elements 8, 9, 10 - 1, 2, 3 = 7, 7, 7. But the result is different if the second sequence is out of order 8, 9, 10, 11, 12 - 1, 2, 3, 1, 2 = 7, 7, 7, 10, 10.

So the first dense_rank() over everything gives you the first running sequence. The second dense_rank() partitioned over the car names gives you the second. When the numbers in it are sequential we have the same result, but every time it "breaks" because another partition is interleaved, the number changes.

Finally, you put the car name together with the number and that gives you something with no real meaning, but that's equal for all items on a sequence with no gaps.

Given that, just group by it and you're done :)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.