Statistika nejcastejsich vyskytu – MySQL – Fórum – Programujte.com

Takze mam tabulku `Tabulka` se sloupcema `ZaznamID`  a `Hodnota` typu integer
tento prikaz vypise ktera hodnota se zaznamem s ID 1 se v tabulce vyskytuje nejcasteji
SELECT `Hodnota` FROM `Tabulka` WHERE `ZaznamID`=1 GROUP BY `Hodnota` ORDER BY COUNT(`Hodnota`) DESC LIMIT 1;

Druha tabulka `Zaznam` obsahuje sloupec `ID`
tento prikaz by mel udelat statistiku nejcastejsiho vyskytu hodnot pro vsechny zaznamy
SELECT `ID`,
(
SELECT `Tabulka.Hodnota` FROM `Tabulka` WHERE `Tabulka.ZaznamID`=`Zaznam.ID` GROUP BY `Tabulka.Hodnota` ORDER BY COUNT(`Tabulka.Hodnota`) DESC LIMIT 1
) AS `Hodnota`
From `Zaznamy`;

Prvni radek vrati jeste spravne, ale vsechny ostatni jsou vsecky stejne a vetsinou spatne, pritom pokud ten prvni prikaz poustim po jednom se stejnym ID tak vraci vzdy prvni spravnou hodnotu, nepremyslejte nad tim jestli neni spatne navrzena struktura tabulek, ve skutecnosti je tam mnohem vic sloupcu a dat, ale pro rychlejsi pochopeni jsem to cele hodne zjednodusil. V cem moze byt chyba ?

 Můžeš zkusit toto:

SELECT Zaznam.ID, Tabulka.Hodnota, Count(Tabulka.Hodnota) as pocet
FROM Tabulka, Zaznam
WHERE Tabulka.ZaznamID=Zaznam.ID
GROUP BY Tabulka.Hodnota
ORDER BY pocet DESC;

No pokud sem to dobre pochopil tak to ve druhem sloupci vypise seznam vsech hodnot  a u kazde potom v prvnim sloupci u ktereho zaznamu jich bylo nejvic, ale potreboval bych aby to vypsalo vsechny zaznamy a u kazdeho zaznamu ktera tam byla nejcasteji. Ale mozna ten tvuj dotaz po mensi uprave udela to co potrebuju, jeste se na to musim mrknout.

Ne me to vypise proste treba

ZaznamID | Hodnota | Pocet

       2             2            6

       5             1            3

       3             3            1

cili ani ne tak u vsech zaznamu jako spis vsechny hodnoty

Potreboval bych to takto nejak,

ZaznamID | Hodnota | Pocet

       1             a            x

       2             b            y

       3             c            z

Nechapu proc ten muj prikaz nahore s vnorenym dotazem nefunguje, kdyz ho spoustim po jednom radku pokazde s jinym zaznam id tak to vypise spravnou nejcastejsi hodnotu, ale kdyz to potom spustim zaroven s tim ze id zaznamu vymenim za id vybrane z tabulky zaznamy tak to udela jen prvni radek, prijde me, ze to je nejaka chyba, nebo je to zpusobeno nejakym jinym vnitrnim fungovanim nez jake bych normalne ocekaval

Tak to zkusím ještě jednou:

SELECT Zaznam.ID, Tabulka.Hodnota, Count(Zaznam.ID)
FROM Tabulka, Zaznam
WHERE Tabulka.ZaznamID=Zaznam.ID
GROUP BY Zaznam.ID
ORDER BY Zaznam.ID ASC;

Tu hodnotu to vraci spatne, mozna proto ze ten count vraci celkovy pocet hodnot a ne pocet nejcastejsich hodnot

no ten COUNT na to nema zadny vliv v tomto pripade to tu hodnotu vraci prvni co je v tabulce, myslim, ze takto jednoduse to nepujde, na nejaky vnoreny dotaz bych to videl

Možná by stálo za to, kdyby si sem dal nějaký příklad dat a jejich výstupu, který očekáváš. Původně jsem si myslel, že vím, o co ti jde, ale teď už vím, že vím prd ;)

Ok, tak napriklad mam:

Tabulka

ID | Hodnota | ZaznamID

1 |      5      |       1

2 |      5      |       1

3 |      3      |       1

4 |      1      |       1

5 |      1      |       2

6 |      1      |       2

7 |      2      |       2

Zaznamy:

ID | Nazev

1 | X

2 | Y

a ted bych potreboval asi tento vystup

Zaznamy.ID | Nejcastejsi Hodnota | Pocet

       1         |              5               |     2

       2         |              1               |     2

Jedna se jen o takovou statistiku, nejak extra nutne to nepotrebuju, jen me stale vrta hlavou proc to nefunguje tak jak jsem to napsal poprve i kdyz by melo.

Tak v tom případě máš ten dotaz správně resp. tak nějak by to mohlo být. Zkus tento kosmeticky upravený a případně dej vědět, co to dává za výsledek a jaký bys čekal:

SELECT ID as iid,
(
	SELECT Count(Hodnota) as pocet
	FROM Tabulka 
	WHERE ZaznamID=iid
	GROUP BY Hodnota
	ORDER BY pocet DESC
        LIMIT 1
) AS col_pocet
From Zaznamy;

Vraci me to uplne to stejne co ten muj, tedy:

iid | col_pocet
1  |       10
2  |        3
3  |        3
4  |        3
5  |        3
6  |        3
7  |        3
8  |        3
9  |        3
10 |       3
11 |       3

kdezto kdyz to poustim po jednom timto prikazem s tim ze menim ZaznamID u WHERE

SELECT `Hodnota` FROM `Tabulka` WHERE `ZaznamID`=1 GROUP BY `Hodnota` ORDER BY COUNT(`Hodnota`) DESC LIMIT 1;

tak to vraci pro ZaznamID

1 -> 10

2 -> 11

3 -> 9

4 -> 10

5 -> 9

6 -> 10

7 -> 12

8 -> 11

9 -> 13

10 -> 12

11 -> 11

Takze prvni radek je spravne a pak ostatni jsou vsecky stejne a spatne, nedokazu si to nijak vysvetlit, ale kazdopadne dik za snahu. :)

Dokonce jsem si to i zkoušel (na nějaké svojí tabulce, kde se opakují data) a mě to funguje správně ... takže víc neporadím.

poustim to na localu, zkusim to jeste na nejakem jinem serveru, treba tam to pojede, ale jestli moze byt chyba v nejakem nastaveni serveru ktere sem ale nemenil netusim

Stejny vysledek, nahodim sem svoje data

CREATE TABLE IF NOT EXISTS `Tabulka` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Hodnota` int(11) NOT NULL,
  `ZaznamID` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=642 ;

CREATE TABLE IF NOT EXISTS `Zaznamy` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Nazev` varchar(100) NOT NULL,
  PRIMARY KEY (`ID`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

INSERT INTO `Tabulka` (`ID`, `Hodnota`, `ZaznamID`) VALUES
(1, 10, 1),
(2, 6, 1),
(3, 8, 1),
(4, 5, 1),
(5, 6, 1),
(6, 5, 1),
(7, 8, 1),
(8, 10, 1),
(9, 7, 1),
(10, 13, 1),
(11, 2, 1),
(12, 6, 1),
(13, 8, 1),
(14, 11, 1),
(15, 12, 1),
(16, 9, 1),
(17, 8, 1),
(18, 9, 1),
(19, 9, 1),
(20, 6, 1),
(21, 6, 1),
(22, 8, 1),
(23, 3, 1),
(24, 4, 1),
(25, 11, 1),
(26, 10, 1),
(27, 6, 1),
(28, 5, 1),
(29, 8, 1),
(30, 9, 1),
(31, 10, 1),
(32, 9, 1),
(33, 1, 1),
(34, 6, 1),
(35, 9, 1),
(36, 12, 1),
(37, 8, 1),
(38, 12, 1),
(39, 6, 1),
(40, 6, 1),
(41, 1, 1),
(42, 9, 1),
(43, 8, 1),
(44, 8, 1),
(45, 8, 1),
(46, 11, 1),
(47, 9, 1),
(48, 1, 1),
(49, 5, 1),
(50, 10, 1),
(51, 6, 1),
(52, 8, 2),
(53, 6, 2),
(54, 8, 2),
(55, 4, 2),
(56, 13, 2),
(57, 5, 2),
(58, 5, 2),
(59, 10, 2),
(60, 6, 2),
(61, 10, 2),
(62, 5, 2),
(63, 13, 2),
(64, 8, 2),
(65, 14, 2),
(66, 15, 2),
(67, 9, 2),
(68, 8, 2),
(69, 6, 2),
(70, 12, 2),
(71, 1, 2),
(72, 9, 2),
(73, 6, 2),
(74, 8, 2),
(75, 6, 2),
(76, 8, 2),
(77, 9, 2),
(78, 3, 2),
(79, 10, 2),
(80, 10, 2),
(81, 8, 2),
(82, 2, 2),
(83, 6, 2),
(84, 5, 2),
(85, 8, 2),
(86, 9, 2),
(87, 16, 2),
(88, 5, 2),
(89, 5, 2),
(90, 6, 2),
(91, 17, 2),
(92, 6, 2),
(93, 8, 2),
(94, 10, 2),
(95, 1, 2),
(96, 8, 2),
(97, 5, 2),
(98, 10, 2),
(99, 8, 2),
(100, 12, 2),
(101, 1, 2),
(102, 1, 2),
(103, 10, 3),
(104, 6, 3),
(105, 8, 3),
(106, 13, 3),
(107, 6, 3),
(108, 13, 3),
(109, 5, 3),
(110, 5, 3),
(111, 18, 3),
(112, 15, 3),
(113, 6, 3),
(114, 9, 3),
(115, 13, 3),
(116, 8, 3),
(117, 1, 3),
(118, 14, 3),
(119, 8, 3),
(120, 5, 3),
(121, 6, 3),
(122, 9, 3),
(123, 6, 3),
(124, 16, 3),
(125, 6, 3),
(126, 8, 3),
(127, 8, 3),
(128, 3, 3),
(129, 8, 3),
(130, 1, 3),
(131, 9, 3),
(132, 10, 3),
(133, 22, 3),
(134, 14, 3),
(135, 8, 3),
(136, 6, 3),
(137, 2, 3),
(138, 6, 3),
(139, 16, 3),
(140, 9, 3),
(141, 20, 3),
(142, 8, 3),
(143, 9, 3),
(144, 18, 3),
(145, 9, 3),
(146, 8, 3),
(147, 19, 3),
(148, 5, 3),
(149, 21, 3),
(150, 11, 3),
(151, 5, 3),
(152, 1, 3),
(153, 1, 3),
(154, 1, 3),
(155, 10, 3),
(156, 6, 4),
(157, 10, 4),
(158, 8, 4),
(159, 9, 4),
(160, 8, 4),
(161, 6, 4),
(162, 8, 4),
(163, 4, 4),
(164, 5, 4),
(165, 5, 4),
(166, 14, 4),
(167, 18, 4),
(168, 1, 4),
(169, 5, 4),
(170, 1, 4),
(171, 6, 4),
(172, 1, 4),
(173, 9, 4),
(174, 13, 4),
(175, 16, 4),
(176, 8, 4),
(177, 10, 4),
(178, 7, 4),
(179, 6, 4),
(180, 12, 4),
(181, 6, 4),
(182, 6, 4),
(183, 6, 4),
(184, 3, 4),
(185, 9, 4),
(186, 9, 4),
(187, 9, 4),
(188, 14, 4),
(189, 10, 4),
(190, 22, 4),
(191, 3, 4),
(192, 16, 4),
(193, 6, 4),
(194, 6, 4),
(195, 9, 4),
(196, 17, 4),
(197, 22, 4),
(198, 1, 4),
(199, 5, 4),
(200, 9, 4),
(201, 9, 4),
(202, 10, 4),
(203, 8, 4),
(204, 5, 4),
(205, 4, 4),
(206, 22, 4),
(207, 6, 4),
(208, 2, 4),
(209, 1, 4),
(210, 9, 5),
(211, 8, 5),
(212, 6, 5),
(213, 10, 5),
(214, 8, 5),
(215, 8, 5),
(216, 6, 5),
(217, 5, 5),
(218, 1, 5),
(219, 6, 5),
(220, 4, 5),
(221, 6, 5),
(222, 1, 5),
(223, 18, 5),
(224, 5, 5),
(225, 9, 5),
(226, 1, 5),
(227, 8, 5),
(228, 6, 5),
(229, 8, 5),
(230, 6, 5),
(231, 16, 5),
(232, 10, 5),
(233, 9, 5),
(234, 5, 5),
(235, 6, 5),
(236, 10, 5),
(237, 9, 5),
(238, 3, 5),
(239, 14, 5),
(240, 9, 5),
(241, 6, 5),
(242, 16, 5),
(243, 5, 5),
(244, 3, 5),
(245, 6, 5),
(246, 17, 5),
(247, 9, 5),
(248, 3, 5),
(249, 16, 5),
(250, 10, 5),
(251, 9, 5),
(252, 23, 5),
(253, 9, 5),
(254, 7, 5),
(255, 23, 5),
(256, 8, 5),
(257, 8, 5),
(258, 5, 5),
(259, 22, 5),
(260, 16, 5),
(261, 9, 6),
(262, 8, 6),
(263, 8, 6),
(264, 10, 6),
(265, 5, 6),
(266, 8, 6),
(267, 5, 6),
(268, 1, 6),
(269, 9, 6),
(270, 9, 6),
(271, 8, 6),
(272, 1, 6),
(273, 10, 6),
(274, 5, 6),
(275, 9, 6),
(276, 6, 6),
(277, 8, 6),
(278, 8, 6),
(279, 6, 6),
(280, 5, 6),
(281, 6, 6),
(282, 5, 6),
(283, 16, 6),
(284, 5, 6),
(285, 9, 6),
(286, 20, 6),
(287, 9, 6),
(288, 3, 6),
(289, 9, 6),
(290, 5, 6),
(291, 9, 6),
(292, 8, 6),
(293, 6, 6),
(294, 7, 6),
(295, 3, 6),
(296, 17, 6),
(297, 17, 6),
(298, 4, 6),
(299, 1, 6),
(300, 9, 6),
(301, 2, 6),
(302, 5, 6),
(303, 24, 6),
(304, 5, 6),
(305, 9, 6),
(306, 12, 6),
(307, 9, 7),
(308, 8, 7),
(309, 8, 7),
(310, 6, 7),
(311, 8, 7),
(312, 9, 7),
(313, 5, 7),
(314, 8, 7),
(315, 1, 7),
(316, 6, 7),
(317, 9, 7),
(318, 6, 7),
(319, 12, 7),
(320, 9, 7),
(321, 10, 7),
(322, 13, 7),
(323, 7, 7),
(324, 1, 7),
(325, 5, 7),
(326, 5, 7),
(327, 1, 7),
(328, 9, 7),
(329, 9, 7),
(330, 6, 7),
(331, 12, 7),
(332, 8, 7),
(333, 13, 7),
(334, 10, 7),
(335, 6, 7),
(336, 6, 7),
(337, 5, 7),
(338, 17, 7),
(339, 1, 7),
(340, 6, 7),
(341, 10, 7),
(342, 9, 7),
(343, 1, 7),
(344, 6, 7),
(345, 3, 7),
(346, 10, 7),
(347, 20, 7),
(348, 25, 7),
(349, 8, 7),
(350, 9, 7),
(351, 9, 7),
(352, 3, 7),
(353, 25, 7),
(354, 4, 7),
(355, 5, 7),
(356, 22, 7),
(357, 27, 7),
(358, 8, 7),
(359, 8, 7),
(360, 6, 7),
(361, 8, 7),
(362, 5, 7),
(363, 8, 7),
(364, 6, 7),
(365, 3, 7),
(366, 9, 7),
(367, 9, 7),
(368, 16, 7),
(369, 6, 7),
(370, 9, 7),
(371, 5, 7),
(372, 17, 7),
(373, 1, 7),
(374, 13, 7),
(375, 8, 7),
(376, 1, 7),
(377, 5, 7),
(378, 26, 7),
(379, 7, 7),
(380, 9, 8),
(381, 8, 8),
(382, 8, 8),
(383, 1, 8),
(384, 6, 8),
(385, 6, 8),
(386, 10, 8),
(387, 8, 8),
(388, 5, 8),
(389, 5, 8),
(390, 9, 8),
(391, 9, 8),
(392, 6, 8),
(393, 7, 8),
(394, 9, 8),
(395, 9, 8),
(396, 10, 8),
(397, 10, 8),
(398, 1, 8),
(399, 13, 8),
(400, 1, 8),
(401, 6, 8),
(402, 3, 8),
(403, 8, 8),
(404, 9, 8),
(405, 6, 8),
(406, 9, 8),
(407, 10, 8),
(408, 18, 8),
(409, 5, 8),
(410, 8, 8),
(411, 5, 8),
(412, 10, 8),
(413, 9, 8),
(414, 5, 8),
(415, 12, 8),
(416, 8, 8),
(417, 6, 8),
(418, 10, 8),
(419, 5, 8),
(420, 1, 8),
(421, 3, 8),
(422, 5, 8),
(423, 12, 8),
(424, 20, 8),
(425, 10, 8),
(426, 8, 8),
(427, 8, 8),
(428, 10, 8),
(429, 6, 8),
(430, 8, 8),
(431, 6, 8),
(432, 3, 8),
(433, 16, 8),
(434, 9, 8),
(435, 1, 8),
(436, 6, 8),
(437, 5, 8),
(438, 6, 8),
(439, 8, 8),
(440, 22, 8),
(441, 6, 8),
(442, 1, 8),
(443, 8, 8),
(444, 9, 9),
(445, 8, 9),
(446, 6, 9),
(447, 8, 9),
(448, 1, 9),
(449, 6, 9),
(450, 8, 9),
(451, 10, 9),
(452, 5, 9),
(453, 8, 9),
(454, 5, 9),
(455, 9, 9),
(456, 8, 9),
(457, 9, 9),
(458, 12, 9),
(459, 6, 9),
(460, 7, 9),
(461, 3, 9),
(462, 1, 9),
(463, 6, 9),
(464, 10, 9),
(465, 13, 9),
(466, 1, 9),
(467, 5, 9),
(468, 9, 9),
(469, 9, 9),
(470, 6, 9),
(471, 7, 9),
(472, 9, 9),
(473, 8, 9),
(474, 18, 9),
(475, 5, 9),
(476, 8, 9),
(477, 1, 9),
(478, 1, 9),
(479, 6, 9),
(480, 8, 9),
(481, 10, 9),
(482, 5, 9),
(483, 3, 9),
(484, 12, 9),
(485, 6, 9),
(486, 8, 9),
(487, 8, 9),
(488, 1, 9),
(489, 8, 9),
(490, 3, 9),
(491, 6, 9),
(492, 5, 9),
(493, 1, 9),
(494, 8, 9),
(495, 5, 9),
(496, 13, 9),
(497, 11, 9),
(498, 6, 9),
(499, 9, 9),
(500, 8, 9),
(501, 9, 9),
(502, 5, 9),
(503, 1, 9),
(504, 9, 10),
(505, 8, 10),
(506, 6, 10),
(507, 8, 10),
(508, 6, 10),
(509, 1, 10),
(510, 8, 10),
(511, 28, 10),
(512, 5, 10),
(513, 8, 10),
(514, 12, 10),
(515, 9, 10),
(516, 5, 10),
(517, 8, 10),
(518, 8, 10),
(519, 9, 10),
(520, 6, 10),
(521, 7, 10),
(522, 3, 10),
(523, 9, 10),
(524, 5, 10),
(525, 13, 10),
(526, 6, 10),
(527, 1, 10),
(528, 10, 10),
(529, 6, 10),
(530, 9, 10),
(531, 9, 10),
(532, 8, 10),
(533, 18, 10),
(534, 5, 10),
(535, 9, 10),
(536, 1, 10),
(537, 5, 10),
(538, 5, 10),
(539, 4, 10),
(540, 8, 10),
(541, 10, 10),
(542, 5, 10),
(543, 16, 10),
(544, 8, 10),
(545, 1, 10),
(546, 10, 10),
(547, 6, 10),
(548, 5, 10),
(549, 12, 10),
(550, 3, 10),
(551, 5, 10),
(552, 16, 10),
(553, 8, 10),
(554, 6, 10),
(555, 8, 10),
(556, 3, 10),
(557, 3, 10),
(558, 4, 10),
(559, 17, 10),
(560, 17, 10),
(561, 16, 10),
(562, 16, 10),
(563, 8, 10),
(564, 6, 10),
(565, 6, 10),
(566, 13, 10),
(567, 6, 10),
(568, 9, 10),
(569, 17, 10),
(570, 9, 10),
(571, 4, 10),
(572, 23, 10),
(573, 22, 10),
(574, 21, 10),
(575, 9, 11),
(576, 8, 11),
(577, 8, 11),
(578, 6, 11),
(579, 6, 11),
(580, 8, 11),
(581, 1, 11),
(582, 8, 11),
(583, 9, 11),
(584, 5, 11),
(585, 12, 11),
(586, 28, 11),
(587, 9, 11),
(588, 9, 11),
(589, 9, 11),
(590, 6, 11),
(591, 7, 11),
(592, 8, 11),
(593, 3, 11),
(594, 5, 11),
(595, 1, 11),
(596, 13, 11),
(597, 6, 11),
(598, 11, 11),
(599, 16, 11),
(600, 7, 11),
(601, 5, 11),
(602, 9, 11),
(603, 9, 11),
(604, 8, 11),
(605, 5, 11),
(606, 5, 11),
(607, 1, 11),
(608, 7, 11),
(609, 8, 11),
(610, 8, 11),
(611, 10, 11),
(612, 5, 11),
(613, 6, 11),
(614, 5, 11),
(615, 5, 11),
(616, 5, 11),
(617, 3, 11),
(618, 5, 11),
(619, 29, 11),
(620, 16, 11),
(621, 6, 11),
(622, 8, 11),
(623, 8, 11),
(624, 3, 11),
(625, 8, 11),
(626, 3, 11),
(627, 16, 11),
(628, 10, 11),
(629, 2, 11),
(630, 6, 11),
(631, 6, 11),
(632, 13, 11),
(633, 6, 11),
(634, 6, 11),
(635, 24, 11),
(636, 17, 11),
(637, 9, 11),
(638, 10, 11),
(639, 5, 11),
(640, 1, 11),
(641, 10, 11);

INSERT INTO `Kalendar` (`ID`, `Jmeno`) VALUES
(1, 'a'),
(2, 'b),
(3, 'c'),
(4, 'd'),
(5, 'e'),
(6, 'f'),
(7, 'g'),
(8, 'h'),
(9, 'i'),
(10, 'j'),
(11, 'k');

No je v tom prikazu par syntaktickych chyb, nekde jsou navic carky nekde chybi uvozovky, u druhe tabulky sem nechal puvodni nazvy je to tim jak sem to menil aby tam nebyly prebytecne data a sloupce ktere nejsou potreba, ale co je zajimavejsi, ze kdyz jsem si ten prikaz spustil na teto zjednodusene verzi tak to tak jak ma. Pritom moje pracovni tabulka je v podstate uplne stejna.

*tak to funguje tak jak ma

Tak vyresene, tabulky sem si exportoval, vymazal je z databaze pak je tam znova vlozil a uz to funguje. Zajimavy problem, diky moc za rady.