Hi All,
Simon answered:
Here's a less gruesome version - no cases. I've given no thought to
performance comparisons.
Thanks for the two great solutions you posted.
Upon further investigation, those solutions assume that we want all
like occurrences together, effectively sorting records in way that
overrides the original view or table.
The best solutions I've come up with so far require first enumerating
the rows in the view (or table). In my separate email thread
"Enumerating rows in a view", I mention two methods:
1. Deconstructing the "order by", replacing it with a series of
inequality operators. Then counting how many other records are less
than each current record.
or:
2. Creating a temporary table, filling with the rows from the view,
using the automatic rowid as the enumeration.
So, for the example "Timetable" in this thread, where I said:
and a view that sorts the data is:
create view "Timetable Sorted"
as
select rowid, Day, Subject, Room, Teacher, Period
from Timetable
order by Day, Period
;
enumerating the rows via each method would be:
1. Deconstructing order into inequalities:
create view Enumerated
as
select count(*) as Sequence, *
from Timetable as Current
left join Timetable as Others
where Current.Day > Others.Day or ( Current.Day = Others.Day and
Current.Period >= Others.Period )
group by Current.Day, Current.Period
;
or:
2. Creating a temporary table:
create temporary table Enumerated1
as
select * from "Timetable Sorted"
;
create temporary view Enumerated
as
select rowid as Sequence, * from Enumerated1
;
Note, I've also used a temporary view just to provide a Sequence
column containing the enumeration. This gives the same column name as
method 1, so the actual Sparsing (below) can use the same syntax (ie
Sequence instead of rowid).
Each method gives the sorted view/table Enumerated as:
Sequence Day Period Teacher Room Subject
---------- ---------- ---------- ---------- ---------- ----------
1 Monday 1 Ng A1 English
2 Monday 2 Peters A2 Maths
3 Monday 3 Peters A2 Computing
4 Monday 4 Kent H1 Sport
5 Tuesday 1 Peters A2 Maths
6 Tuesday 2 Ng A1 History
7 Tuesday 3 Ng A1 English
8 Tuesday 4 Ng A1 History
9 Wednesday 1 Peters A2 Maths
10 Wednesday 2 Kent H1 Sport
11 Wednesday 3 Who S2 Science
12 Wednesday 4 Smith S2 Science
Now, on to replacing repeated values with null (ie showing a "sparse
matrix") and counting the repetitions. Now that I have enumerated
sorted rows, I can use the following method. I compare each Current
value with the Previous value (ie the value that is enumerated as one
less than the Current value). If it's the same as the previous, then
it's a repetition which I replace with null. If it's different to the
previous, then I show the value and the count of the same values from
the current to the next change, or the bottom of the table.
For the Day column, for example, I calculate the Day (Current.Day or
null) and DayCount (count until the next change, or null) like this:
select
case
when Previous.Day is null or Current.Day != Previous.Day
then Current.Day
else null
end
as Day,
case
when Previous.Day is null or Current.Day != Previous.Day
then
coalesce(
(
select Sequence from Enumerated as Others
where Current.Day != Others.Day and Others.Sequence >=
Current.Sequence limit 1
),
Bottom
) - Current.Sequence
else null
end
as DayCount
from Enumerated as Current
left join Enumerated as Previous on Current.Sequence - 1 =
Previous.Sequence
left join ( select max( Sequence ) + 1 as Bottom from Enumerated )
;
which gives the desired:
Day DayCount
---------- ----------
Monday 4
. .
. .
. .
Tuesday 4
. .
. .
. .
Wednesday 4
. .
. .
. .
(I've used . to show nulls)
To build the complete matrix, ie for all columns, I duplicated the
expressions above for Day and DayCount to make Room and RoomCount
(replacing Day with Room), Subject and SubjectCount etc. The result
looks like the desired:
Day DC Room RC Subject SC Teacher TC Period
---------- --- ---- --- ---------- --- ---------- --- ------
Monday 4 A1 1 English 1 Ng 1 1
. . A2 2 Maths 1 Peters 2 2
. . . . Computing 1 . . 3
. . H1 1 Sport 1 Kent 1 4
Tuesday 4 A2 1 Maths 1 Peters 1 1
. . A1 3 History 1 Ng 3 2
. . . . English 1 . . 3
. . . . History 1 . . 4
Wednesday 4 A2 1 Maths 1 Peters 1 1
. . H1 1 Sport 1 Kent 1 2
. . S2 2 Science 2 Who 1 3
. . . . . . Smith 1 4
If anyone has an improvement or a better way, I welcome it. If not,
then I hope this helps the next person looking for a solution.
Below is the complete solution to the Timetable problem. Most of it is
just the same thing repeated for each column.
Tom
.mode columns
.width 10 3 4 3 10 3 10 3 3
.headers on
.nullvalue .
begin transaction
;
create temporary table Enumerated1
as
select * from "Timetable Sorted"
;
create temporary view Enumerated
as
select rowid as Sequence, * from Enumerated1
;
select
case
when Previous.Day is null or Current.Day != Previous.Day
then Current.Day
else null
end
as Day,
case
when Previous.Day is null or Current.Day != Previous.Day
then
coalesce(
(
select Sequence from Enumerated as Others
where Current.Day != Others.Day and Others.Sequence >=
Current.Sequence limit 1
),
Bottom
) - Current.Sequence
else null
end
as DC,
case
when Previous.Room is null or Current.Room != Previous.Room
then Current.Room
else null
end
as Room,
case
when Previous.Room is null or Current.Room != Previous.Room
then
coalesce(
(
select Sequence from Enumerated as Others
where Current.Room != Others.Room and Others.Sequence
>= Current.Sequence limit 1
),
Bottom
) - Current.Sequence
else null
end
as RC,
case
when Previous.Subject is null or Current.Subject !=
Previous.Subject
then Current.Subject
else null
end
as Subject,
case
when Previous.Subject is null or Current.Subject !=
Previous.Subject
then
coalesce(
(
select Sequence from Enumerated as Others
where Current.Subject != Others.Subject and
Others.Sequence >= Current.Sequence limit 1
),
Bottom
) - Current.Sequence
else null
end
as SC,
case
when Previous.Teacher is null or Current.Teacher !=
Previous.Teacher
then Current.Teacher
else null
end
as Teacher,
case
when Previous.Teacher is null or Current.Teacher !=
Previous.Teacher
then
coalesce(
(
select Sequence from Enumerated as Others
where Current.Teacher != Others.Teacher and
Others.Sequence >= Current.Sequence limit 1
),
Bottom
) - Current.Sequence
else null
end
as TC,
Current.Period
as Period
from Enumerated as Current
left join Enumerated as Previous on Current.Sequence - 1 =
Previous.Sequence
left join ( select max( Sequence ) + 1 as Bottom from Enumerated )
;
commit
;
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
