J'ai un système de suivi des clics simple qui se compose de trois tableaux "suivi" (qui contient des vues uniques), "vues" (qui contient des vues brutes) et "produits" (qui contient des produits).

Voici comment cela fonctionne : à chaque fois qu'un internaute clique sur un lien de tracking, si le hash présent dans le lien n'existe pas dans la base de données, il sera enregistré dans la table "tracking" en tant que vue unique et également dans les "vues" table comme une vue brute. Si le hachage présent dans le lien existe dans la base de données, alors il ne sera enregistré que dans la table "vues". Donc, fondamentalement, le nombre de "vues brutes" ne peut pas être inférieur au nombre de "vues uniques" car chaque "vue unique" compte également comme une "vue brute".

J'ai écrit une requête pour créer des rapports basés sur les produits, mais le nombre de "vues brutes" renvoyé n'est pas correct.

J'ai également créé un fiddle qui, j'espère, donnera un meilleur aperçu de mon problème.

Voici la structure du tableau:

CREATE TABLE `products` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(128) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `products` (`id`, `name`) VALUES
(1, 'Test product');

CREATE TABLE `tracking` (
  `id` int(10) UNSIGNED NOT NULL,
  `product_id` int(11) NOT NULL,
  `hash` varchar(32) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tracking` (`id`, `product_id`, `hash`, `created`) VALUES
(1, 1, '7ddf32e17a6ac5ce04a8ecbf782ca509', '2020-02-09 18:50:19'),
(2, 1, '00bb28eaf259ba0c932d67f649d90783', '2020-02-09 18:55:34');

CREATE TABLE `views` (
  `id` int(10) UNSIGNED NOT NULL,
  `hash` varchar(32) NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `views` (`id`, `hash`, `created`) VALUES
(1, '7ddf32e17a6ac5ce04a8ecbf782ca509', '2020-02-09 18:46:30'),
(2, '7ddf32e17a6ac5ce04a8ecbf782ca509', '2020-02-09 18:46:30'),
(3, '7ddf32e17a6ac5ce04a8ecbf782ca509', '2020-02-09 18:46:35'),
(4, '7ddf32e17a6ac5ce04a8ecbf782ca509', '2020-02-09 18:46:42'),
(5, '00bb28eaf259ba0c932d67f649d90783', '2020-02-09 18:56:31'),
(6, '00bb28eaf259ba0c932d67f649d90783', '2020-02-09 18:57:01');

Et voici la requête que j'ai écrite jusqu'à présent:

SELECT products.name AS `param`,
SUM(IF(tracking.product_id<>24, 1, 0)) AS `uniques`,
IF(SUM(IF(tracking.product_id<>24, 1, 0))=0, 0,
            (SELECT COUNT(`hash`)
             FROM `views` WHERE tracking.hash = views.hash)) AS `views`
FROM tracking
LEFT JOIN products ON products.id = tracking.product_id
WHERE tracking.created BETWEEN '2019-01-01 00:00:00' AND '2020-02-10 00:00:00'
GROUP BY products.name

Comme vous pouvez le voir, j'ai 2 vues uniques et 6 vues brutes (4 pour un hachage et 2 pour l'autre).

Mon attente serait que le résultat de la requête soit 2 vues uniques et 6 vues brutes pour ce produit donné, mais à la place, j'obtiens 2 vues uniques et 4 vues brutes. Comme s'il comptait les vues uniquement pour le premier hachage.

1
Cosmin 9 févr. 2020 à 22:16

1 réponse

Meilleure réponse

La requête suivante peut résoudre votre situation :

SELECT 
    products.name,
    COUNT(DISTINCT  `tracking`.`hash`) AS  `uniques`, -- count unique hashes
    COUNT(*) AS  `views` -- count total
FROM `tracking`
JOIN `views` ON `views`.hash = tracking.hash
LEFT JOIN products ON products.id = tracking.product_id
WHERE tracking.created BETWEEN '2019-01-01 00:00:00' AND '2020-02-10 00:00:00'
GROUP BY products.name;
;
1
Slava Rozhnev 9 févr. 2020 à 20:23