CODEWITHSUNDEEP
mysqlsql
Alec Teal
Alec Teal

Reputation: 5918

MySQL - creating more results than one starts with (example: integers within an interval)

I could fall back to a procedure with a while loop (maybe, I've never tried to do this before) but there must be a faster way.

[backspaced stuff - I'll just show describes]

CREATE TABLE  `table` (
  `ZoneId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `VolumeId` int(10) unsigned NOT NULL,
  `TypeId` int(10) unsigned NOT NULL,
  `ExtraTypeInfo` int(10) unsigned NOT NULL,
  `Time` int(10) unsigned NOT NULL,
  `StartIndex` int(10) unsigned NOT NULL,
  `EndIndex` int(10) unsigned NOT NULL,
  `SectionId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ZoneId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

StartIndex and EndIndex denote a range, it is saying everything from start until the end has the properties this record describes (say a range covers 300,000 this is better than 300,000 rows, also the items don't need IDs, their position is their ID)

Some indices will be covered twice, for example the range [1,20] and [1,1] with TypeId 0 means that 1 has been (whatever it denotes) twice, and 2-20 inclusive just once, the rest 0 times.

Rather than reading the table and keeping a manual tally I'd rather shift the work to MySQL, I (sadly) must use PHP and it uses too much memory if I use an array or object per entry, and takes too long if I use a bitfield.

Rather than being tossed a query and using it I'd rather learn how to do this, so I would love a solution has three columns:

section | index | count0 | count1

Where count0 is the number of times intervals with TypeId 0 has covered the index, and count1 where it is 1. I have asked for both to prevent solutions where this'd be done as two queries. (In the field this will be subject to a given VolumeId)

The length of a section is of course known

Example

Suppose we have (ignoring ZoneId,ExtraTypeInfo and Time for simplicity) with this dataset:

VolId TypeId Start End Section
1     0      1     2   1
1     0      2     2   1
2     0      1     1   1
1     1      2     3   1
1     0      1     2   2

To output (subject to VolId = 1)

sec ind c0 c1
1   1   1  0
1   2   2  1
1   3   1  1
1   4   0  0  <--notice this, we do know the length of sec 1 and 4 was covered by nothing
2   1   1  0
2   2   1  0
2   3   0  0
2   4   0  0
2   5   0  0

Indices may start at 0 or 1, it is easy to change and as yet undecided (starting at 1 is most natural for the application, 0 helps only with the code)

I hope SQL can do this. I will carry on reading but I'm not sure what to do, as I mentioned at the top I am thinking of a procedure that runs over all sections that calls a procedure to generate lists from 1 to length, and then searching for each of those and hoping MySQL's optimiser spots what is going on.

Upvotes: 3

Views: 61

Answers (1)

eggyal
eggyal

Reputation: 125875

Further to our conversation, I suggest that you create a permanent number_table that contains every possible index value:

CREATE TABLE number_table (ind SMALLINT UNSIGNED PRIMARY KEY)
SELECT bF.v|bE.v|bD.v|bC.v|bB.v|bA.v|b9.v|b8.v
      |b7.v|b6.v|b5.v|b4.v|b3.v|b2.v|b1.v|b0.v ind
FROM
  (SELECT 0 v UNION ALL SELECT 1<<0x0) b0,
  (SELECT 0 v UNION ALL SELECT 1<<0x1) b1,
  (SELECT 0 v UNION ALL SELECT 1<<0x2) b2,
  (SELECT 0 v UNION ALL SELECT 1<<0x3) b3,
  (SELECT 0 v UNION ALL SELECT 1<<0x4) b4,
  (SELECT 0 v UNION ALL SELECT 1<<0x5) b5,
  (SELECT 0 v UNION ALL SELECT 1<<0x6) b6,
  (SELECT 0 v UNION ALL SELECT 1<<0x7) b7,
  (SELECT 0 v UNION ALL SELECT 1<<0x8) b8,
  (SELECT 0 v UNION ALL SELECT 1<<0x9) b9,
  (SELECT 0 v UNION ALL SELECT 1<<0xA) bA,
  (SELECT 0 v UNION ALL SELECT 1<<0xB) bB,
  (SELECT 0 v UNION ALL SELECT 1<<0xC) bC,
  (SELECT 0 v UNION ALL SELECT 1<<0xD) bD,
  (SELECT 0 v UNION ALL SELECT 1<<0xE) bE,
  (SELECT 0 v UNION ALL SELECT 1<<0xF) bF;
   
DELETE FROM number_table WHERE ind = 0;

Then you can make an outer join between this table and your table above, limiting the results by the maximum length of the relevant section:

SELECT      s.SectionId,
            nt.ind,
            IFNULL(SUM(t.TypeID=0),0) c0,
            IFNULL(SUM(t.TypeID=1),0) c1
FROM        sections s
       JOIN number_table nt
         ON nt.ind <= s.Length
  LEFT JOIN `table` t
         ON t.SectionId = s.SectionId
        AND nt.ind BETWEEN t.StartIndex AND t.EndIndex
        AND t.VolumeId = 1
GROUP BY    s.SectionId, nt.ind

See it on sqlfiddle.

Upvotes: 1

Related Questions