Saskaitīt unikālus ierakstus konkrētā dienā

[AnotherUser]

Sveiki, meklēju effektivāku/ātrāku risinājumu..

Tabulas lauki:
 

+----+-----+------+---------------+
| ID | uId | hits | date_hour     |
+----+-----+------+---------------+
|  1 | 101 |    2 | 2013-12-01 13 |
|  2 | 101 |    5 | 2013-12-01 14 |
|  3 | 101 |    1 | 2013-12-01 15 |
|  4 | 102 |    3 | 2013-12-01 15 |
|  5 | 102 |    1 | 2013-12-02 16 |
|  6 | 103 |    9 | 2013-12-02 17 |
|  7 | 104 |    4 | 2013-12-02 17 |
|  8 | 105 |    2 | 2013-12-03 02 |
|  9 | 106 |    1 | 2013-12-03 10 |
| 10 | 107 |    1 | 2013-12-03 12 |
+----+-----+------+---------------+

Nepieciešams saskaitīt cik unikālie (pa visu laiku) `uId` ir konkrētā dienā, respektīvi no šiem datiem rezultāts būtu sekojoš:
 

2013-12-01  2(unikālie)
2013-12-02  2(unikālie)
2013-12-03  3(unikālie)

Dotajā brīdī atlasu šādi:
 

SELECT COUNT(DISTINCT `uId`) as uniq
FROM `table` as ts
WHERE (SELECT DATE(ts2.`date_hour`)
        FROM `table` as ts2
        WHERE ts.uId = ts2.uId
        ORDER BY ts2.`id`
        LIMIT 1) = :date
AND DATE(ts.`date_hour`) = :date

Paldies.

Edited December 16, 2013 by AnotherUser

[aaxc]

Apmērām šādi:

 

 

SELECT date(substr(date_hour,0,10)), count(id) FROM `table` GROUP BY substr(date_hour,0,10);

 

Šodien grūta darbadiena, tākā sintaxu netestēju, bet idejai vajadzētu būt.

[AnotherUser]

1. Ja tu domāji ar substr izvilkt 'gads-mēnesis-diena', tad jāsāk no 1. pozīcijas.

2. Prikškam izmantot substr + date() ? Dubults neplīst?

3. Dotajs piemērs atlasīs vairākus ierakstus (tik cik unikālie datumi), jo tiek grupēts pēc datuma un noteikti ne-unikālos `uId`

4. Mēģināšu vēlreiz izskaidrot:

 

Tātad nepieciešams saskaitīt unikālos `uId` - tā lai rezultātā man atgriež vienu ierakstu (par konkrēto datumu, kuru es pieprasu) ar skaitu cik unikalie, tajā datumā iekrita.

Unikalais - unikāls `uId` pa visu tabulu(visām dienam), respektīvi ja tabulā eksistē ieraksts ar `uId` - 101, tad tas ir viens unikals ieraksts un citās dienās/mēnešos/gados, mēs viņu vairs neuzskatam par unikalu.

 

Mans piemērs darbojas no loģiskā viedokļa perfekti, bet neapmierina pats tehniskais izpildījums/ātrdarbība, šķiet jābūt kādam elegantākam risinājumam.

Edited December 16, 2013 by AnotherUser

[jurchiks]

Nezinu, varbūt es kaut ko nesaprotu, bet vai šādi nestrādātu:

SELECT COUNT(DISTINCT `uId`)
FROM `table`
WHERE DATE(`date_hour`) = :date

? Edited December 16, 2013 by jurchiks

[AnotherUser]

Unikalais - unikāls `uId` pa visu tabulu(visām dienam), respektīvi ja tabulā eksistē ieraksts ar `uId` - 101, tad tas ir viens unikals ieraksts un citās dienās/mēnešos/gados, mēs viņu vairs neuzskatam par unikalu.

 

 

Nezinu, varbūt es kaut ko nesaprotu, bet vai šādi nestrādātu:

SELECT COUNT(DISTINCT `uId`)
FROM `table`
WHERE DATE(`date_hour`) = :date

?

 

 

Šādi tu dabūsi unikalos tikai tā datuma ietvaros, bet tāds `uId` varbūt jau ir bijis vakar vai gadu atpakaļ ierakstījies..

Edited December 16, 2013 by AnotherUser

[jurchiks]

Da nu, nav man gribēšanas par to tagad domāt. GL.

[briedis]

Teorētiski varētu šitā...

http://sqlfiddle.com/#!2/52ab33/13

SELECT COUNT(d.date), d.date FROM (SELECT date FROM test GROUP BY uid) AS d GROUP BY d.date

Derētu notestēt uz vairāk datiem :)

[jurchiks]

Viņam vajag, lai resultset ir tikai 1 cell - visu tavu count() summa.

[AnotherUser]

Briedi, ņemot tavu domu gājienu par pamatu, manā situācijā tas izskatās sekojoši:

SELECT SUM(dd.`uniq`) as uniq 
FROM (SELECT COUNT(d.`date_hour`) as uniq 
      FROM (SELECT `date_hour` 
	    FROM `table` 
	    WHERE DATE(`date_hour`) = :date
	    GROUP BY `uId`) AS d 
      GROUP BY d.`date_hour`) as dd

..Izpildās 1/3 ilgāk..

 

Vēl varianti?

[jurchiks]

Moš tur kaut kā var iepīt WHERE ... NOT IN (SELECT something FROM somewhere)? Just an idea.

[AnotherUser]

Var!

SELECT COUNT(DISTINCT `uId`) as uniq
FROM `table` as ts 
WHERE ts.`uId` NOT IN (SELECT ts2.`uId` FROM `table` as ts2 WHERE ts2.`date_hour` != ts.`date_hour`) AND DATE(ts.`date_hour`) = '2013-12-15'

Respektīvi kad man tabulā būs 1M ierakstu, iekš NOT IN (būs 1M id'u..), Neder..

 

Nu kur ir mūsu LV, DB Gurū..?

Edited December 16, 2013 by AnotherUser

[marrtins]

Iedo lielāku setu?

[daGrevis]

> Nu kur ir mūsu LV, DB Gurū..?

 

Kas tas tāds?

[briedis]

Nū, ja problēma ir ātrdarbība, tad ir jādomā par indeksiem. Bet tīklīdz tu sāc taisīt prikolus ar funkcijām uz kolonnām (DATE()), tā var aizmirst, ka tur kaut kas jēdzīgā laikā izpildīsies, jo jāiet cauri būs pilnīgi visiem ierakstiem.

 

Neesmu baigais specs, bet pamēģini gan to subselektu, gan arī galveno selektu izpētīt ar EXPLAIN un pielāgot tabulu, lai pilnībā tiktu izmantoti indeksi. Minu, ka arī vajadzēs glabāt tikai datumu atsevišķā kolonnā, lai var normāli grupēt.

[aaxc]

Un neaizmirsti noindexēt:

CREATE TABLE
    `test2`
(
    id serial primary key
    , uid int
    , hits int
    , date_hour datetime
);

INSERT INTO
    `test2`
(
    uid
    , hits
    , date_hour
)
VALUES
    ( 101, 2, '2013-12-01 13' )
    , ( 101, 5, '2013-12-01 14' )
    , ( 101, 1, '2013-12-01 15' )
    , ( 102, 3, '2013-12-01 15' )
    , ( 102, 1, '2013-12-02 16' )
    , ( 103, 9, '2013-12-02 17' )
    , ( 104, 4, '2013-12-02 17' )
    , ( 105, 2, '2013-12-03 02' )
    , ( 106, 1, '2013-12-03 10' )
    , ( 107, 1, '2013-12-03 12' );
    
SELECT
  date( date_hour )
  , count( distinct uid )
FROM
  `test2`
GROUP BY
  date( date_hour )
ORDER BY
  date_hour ASC;

SQL Fiddle