CODEWITHSUNDEEP
mysql
Skjaar
Skjaar

Reputation: 89

Count occurrences that differ within a column

I want to be able to select the amount of times the data in columns Somedata_A and Somedata_B has changed from the from the previous row within its column. I've tried using DISTINCT and it works to some degree. {1,2,3,2,1,1} will show 3 when I want it to show 4 course there's 5 different values in sequence.

Example:

 A,B,C,D,E,F
{1,2,3,2,1,1}

A compare to B gives a difference, B compare to C gives a difference . . . E compare to F gives not difference. All in all it gives 4 differences within a set of 6 values.

I have gotten DISTINCT to work but it does not really do the trick for me. And to add more to the question I'm really not interested it the whole range, lets say just the 2 last days/entries per Title.

Second I'm concern about performance issues. I tried the query below on a real set of data and it got interrupted probably due to timeout.

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE testdata(
Title varchar(10),
Date varchar(10),
Somedata_A int(5),
Somedata_B int(5));


INSERT INTO testdata (Title, Date, Somedata_A, Somedata_B) VALUES
("Alpha", '123', 1, 2), 
("Alpha", '234', 2, 2), 
("Alpha", '345', 1, 2),
("Alpha", '349', 1, 2), 
("Alpha", '456', 1, 2), 
("Omega", '123', 1, 1), 
("Omega", '234', 2, 2), 
("Omega", '345', 3, 3),
("Omega", '349', 4, 3), 
("Omega", '456', 5, 4),
("Delta", '123', 1, 1), 
("Delta", '234', 2, 2), 
("Delta", '345', 1, 3),
("Delta", '349', 2, 3), 
("Delta", '456', 1, 4);

Query 1:

SELECT t.Title, (SELECT COUNT(DISTINCT Somedata_A) FROM testdata AS tt WHERE t.Title = tt.Title) AS A,
    (SELECT COUNT(DISTINCT Somedata_B) FROM testdata AS tt WHERE t.Title = tt.Title) AS B
    FROM testdata AS t
    GROUP BY t.Title

Results:

| TITLE | A | B |
|-------|---|---|
| Alpha | 2 | 1 |
| Delta | 2 | 4 |
| Omega | 5 | 4 |

Upvotes: 1

Views: 71

Answers (2)

xQbert
xQbert

Reputation: 35333

Something like this may work: it uses a variable for row number, joins on an offset of 1 and then counts differences for A and B.

http://sqlfiddle.com/#!2/3bbc8/9/2

set @i = 0;
set @j = 0;

Select 
A.Title aTitle, 
sum(Case when A.SomeData_A <> B.SomeData_A then 1 else 0 end) AVar, 
sum(Case when A.SomeData_B <> B.SomeData_B then 1 else 0 end) BVar
from 
(SELECT Title, @i:=@i+1 as ROWID, SomeData_A, SomeData_B 
FROM testdata
ORDER BY Title, date desc) as A
INNER JOIN 
(SELECT Title, @j:=@j+1 as ROWID, SomeData_A, SomeData_B
FROM testdata
ORDER BY Title, date desc) as B
 ON A.RowID= B.RowID + 1
 AND A.Title=B.Title
Group by A.Title

Upvotes: 1

AgRizzo
AgRizzo

Reputation: 5271

This works (see here) (FYI: Your results in the question do not match your data - for instance, for Alpha, ColumnA: it never changes from 1. The answer should be 0)

Hopefully you can adapt this Statement to your actual data model

SELECT t1.title, SUM(t1.Somedata_A<>t2.Somedata_a) as SomeData_A
  ,SUM(t1.Somedata_b<>t2.Somedata_b) as SomeData_B
FROM testdata AS t1
JOIN testdata AS t2
  ON t1.title = t2.title
    AND t2.date = DATE_ADD(t1.date, INTERVAL 1 DAY)
GROUP BY t1.title
ORDER BY t1.title;

Upvotes: 0

Related Questions