Vienādu vērtību skaitīšana

[klavsr]

Sveiki!
 
Ir jāatrod vienādās vērtības tabulā, šajā gadījumā - cik ierakstos sakrīt e-pasta adreses. To daru šādi:

SELECT email, count(email) as times FROM applications group by email having times > 1 

Šajā gadījumā tiek saskaitītas tās vienādās e-pasta adreses, kuras sakrīt 1:1, bet gribu pieskaitīt arī tās, kur vienā vai otrā galā ir atstarpe, vai lieks punkts, vai kas cits.

Respektīvi, saskaitīt tās vērtības, kas sakristu pēc principa:

LIKE '%email%'

Kā būtu jāizskatās kverijam?

[aaxc]

Ar substr un case / if mēģināji?

[marrtins]

Domāju, no šī varēsi kaut ko izlobīt:

SELECT
	t1.email,
	(SELECT
		GROUP_CONCAT(email)
	FROM
		applications
	WHERE
		LOCATE(email, t1.email) > 0 AND
		LENGTH(email) < LENGTH(t1.email)
	) AS b
FROM
	applications t1
HAVING
	b IS NOT NULL

[Gints Plivna]

Vienmēr jau nu vajadzētu nenoslinkot un iedot skriptu + piemēra datus.

Bet nu es izdarīju pats, jo piemērs likās interesants.

CREATE TABLE applications (
  id int primary key,
  email varchar(100));

insert into applications values (1, 'asd@as.lv ');
insert into applications values (2, ' asd@as.lv. ');
insert into applications values (3, 'asd@as.lv');
insert into applications values (4, 'asd@as.lv ');
insert into applications values (5, 'd@as.lv ');
insert into applications values (6, ' d@as.lv ');
insert into applications values (7, ' d@as.lv.');
insert into applications values (8, 'q@as.lv ');

Nu tātad LIKE '%email%' īsti neder, jo tad asd@as.lv būs tas pats, kas d@as.lv.

 

Tāpēc man šķiet, ka vajadzētu uzrakstīt f-ju, kas katru e-pastu noved līdz nosacītai normālformai, lai tur būtu palicis tikai, tas ko vajag, piemēram:

mysql> select id, trim('.' FROM trim(t1.email)), email
    -> from applications t1;
+----+-------------------------------+--------------+
| id | trim('.' FROM trim(t1.email)) | email        |
+----+-------------------------------+--------------+
|  1 | asd@as.lv                     | asd@as.lv    |
|  2 | asd@as.lv                     |  asd@as.lv.  |
|  3 | asd@as.lv                     | asd@as.lv    |
|  4 | asd@as.lv                     | asd@as.lv    |
|  5 | d@as.lv                       | d@as.lv      |
|  6 | d@as.lv                       |  d@as.lv     |
|  7 | d@as.lv                       |  d@as.lv.    |
|  8 | q@as.lv                       | q@as.lv      |
+----+-------------------------------+--------------+
8 rows in set (0.00 sec)

Un tad jau tālāk var urbt vai nu ar group by:

mysql> select trim('.' FROM trim(t1.email)), count(*)
    -> from applications t1
    -> group by trim('.' FROM trim(t1.email))
    -> having count(*) > 1;
+-------------------------------+----------+
| trim('.' FROM trim(t1.email)) | count(*) |
+-------------------------------+----------+
| asd@as.lv                     |        4 |
| d@as.lv                       |        3 |
+-------------------------------+----------+
2 rows in set (0.00 sec)

vai arī group_concat:

mysql> select trim('.' FROM trim(t1.email)), group_concat(id)
    -> from applications t1
    -> group by trim('.' FROM trim(t1.email))
    -> having count(*) > 1;
+-------------------------------+------------------+
| trim('.' FROM trim(t1.email)) | group_concat(id) |
+-------------------------------+------------------+
| asd@as.lv                     | 1,2,3,4          |
| d@as.lv                       | 5,6,7            |
+-------------------------------+------------------+
2 rows in set (0.00 sec)

mysql> select trim('.' FROM trim(t1.email)), group_concat(email)
    -> from applications t1
    -> group by trim('.' FROM trim(t1.email))
    -> having count(*) > 1;
+-------------------------------+----------------------------------------------+
| trim('.' FROM trim(t1.email)) | group_concat(email)                          |
+-------------------------------+----------------------------------------------+
| asd@as.lv                     | asd@as.lv , asd@as.lv. ,asd@as.lv,asd@as.lv  |
| d@as.lv                       | d@as.lv , d@as.lv , d@as.lv.                 |
+-------------------------------+----------------------------------------------+
2 rows in set (0.00 sec)

Ja neder trimi, tad var meklēt patternu <simbols>@<simbols>.<simbols>, bet cik atceros tad vispārīgā gadījumā e-pasta specifikācija bija nenormāli čakarīgs pasākums un gandrīz katrai regulārajai izteiksmei, kas apraksta e-pastu, var atrast kādu piemēru, kas pēc specifikācijas neder :)

 

Gints Plivna

http://datubazes.wordpress.com

[jurchiks]

gribu pieskaitīt arī tās, kur vienā vai otrā galā ir atstarpe, vai lieks punkts, vai kas cits

Pirmām kārtām jau vajadzētu validēt vai vismaz normalizēt e-pastus pirms tie nonāk datubāzē, lai tāda huiņa nemaisītu gaisu. Pašam pēc tam būtu vieglāk.

Ja nu kaut kāda velna pēc tā nevar, tad vismaz izpildi "UPDATE applications SET email = trim('.' FROM trim(t1.email))".

Edited November 9, 2013 by jurchiks

[aaxc]

jurchik, ne vienmēr ir iespēja kontrolēt kas nonāk db, it īpaši, ja tas ir sākumā domāts kā text input lauks un vēlāk tos datus izdomā izmantot jau kā specifiskus + vēl grūtāk, ja tu pie tā projekta tikai tikko esi piesēdies un sāc lēnām visu optimizēt.

[jurchiks]

Datubāzes kolonnas nosaukums ir email, tāpēc pieņemu, ka tā jau sākotnēji bijusi paredzēta e-pasta saturēšanai. Ja ir iespējams, tad obligāti jāpieliek nepieciešamās pārbaudes/apstrāde, lai nebūtu ar šitādiem sūdiem jāņemās.