Ne mysql sorgu bana bu tablodan ay, toplu istatistikleri elde izin istiyorsunuz?

3 Cevap php

Ben birikmiş her nokta puan tablosunda oturum sitemde, bir puan sistemi kurulum var. Yapısı (kaç bu eylem sırasında biriken puan), ve p_timestamp basit, p_userid, p_points olduğunu.

Ben her ay için, kullanıcıları biriken üst 3 noktayı göstermek istiyorum. Yani aslında, her kullanıcı kimliği için, ay için p_points tabloyu özetlemek ve aylık halinde gruplandırılmış ilk 3 kullanıcıları göstermesi gerekir. Kullanıcı kimlikleri, gerçek kullanıcı adlarını almak için, bir kullanıcının tabloya eşlik edecek.

Ne bunu yapmak için en iyi yol olacağını? Php / mysql kullanabilirsiniz.

EDIT: As a possible solution, I could create another column, and log YYYY-MM into it, and simply group it based on that, but thats more data I gotta log, for an already huge table.

EDIT 2:

Gibi saklanan veriler

INSERT INTO `points` (`point_userid`, `point_points`, `point_code`, `point_date`) VALUES
(8465, 20, 3, 1237337627),
(46745, 20, 3, 1237337678),
(7435, 20, 3, 1237337733),
(46565, 20, 3, 1237337802),
(4466, 20, 3, 1237337836),
(34685, 20, 3, 1237337885),
(8544, 20, 3, 1237337908),
(6454, 20, 3, 1237337998),
(45765, 20, 3, 1237338008),
(3476, 20, 3, 1237338076);

3 Cevap

Bu MySQL kolay değildir.

Önce değişkenleri, geçerli grup depolamak için bir ve grubunda geçerli satır numarasını saklamak için tek bir tablo oluşturmak gerekir. NULL onlara hem başlatılamadı.

Sonra ay grubunu yineleme ve skoru tarafından sipariş edilen tüm satırları seçin ve geçerli rown numarasını seçin ve bunu artırmak. Grup değiştirirse, birine satır sayısını sıfırlamak.

Sonra subselect tüm bu koymak ve dış select, RowNumber <= 3 ile tüm satırları seçin.

Bu sorguyu kullanabilirsiniz:

SELECT month, p_userid, points FROM (
    SELECT
        *,
        (@rn := CASE WHEN month = @last_month THEN @rn + 1 ELSE 1 END) AS rn,
        (@last_month := month)
    FROM (
        SELECT p_userid, month(p_timestamp) AS month, SUM(p_points) AS points
        FROM Table1, (SELECT @last_month := NULL, @rn := 0) AS vars
        GROUP BY p_userid, month(p_timestamp)
        ORDER BY month, points DESC
    ) AS T1
) AS T2
WHERE rn <= 3

Sonuç:

Month User Score
1     4    7
1     3    5
1     2    4
2     4    17
2     5    10
2     3    6

Test verileri:

CREATE TABLE Table1 (p_userid INT NOT NULL,
                     p_points INT NOT NULL,
                     p_timestamp TIMESTAMP NOT NULL);

INSERT INTO Table1 (p_userid, p_points, p_timestamp) VALUES
(1, 1, '2010-01-01'),
(1, 2, '2010-01-02'),
(1, 3, '2010-02-01'),
(2, 4, '2010-01-01'),
(3, 5, '2010-01-01'),
(3, 6, '2010-02-01'),
(4, 7, '2010-01-01'),
(4, 8, '2010-02-01'),
(4, 9, '2010-02-02'),
(5, 10, '2010-02-02');

Hm,

Çok basit mi?

SELECT COUNT(tb1.p_points) as total_points, tb1.p_userid, tb1.p_timestamp, tb2.username 
FROM tb1, tb2 
WHERE tb1.p_userid = tb2.username AND p_timestamp BETWEEN 'start_of_date' AND 'end_of_month' 
GROUP BY p_userid 
ORDER BY total_points DESC LIMIT 3

Sözdizimi (SQL nispeten yeni) biraz dışarı olabilir - aradığınız sonucu elde böyle bir sorgu yineleme değil mi? Mark'ın tepkisi bana bu kesinlikle çok basit olduğunu düşünüyorum yapar itiraf ama ben yine de görelim düşündüm gerekir.

Ben plpgsql bağımlısı ve ben MySQL çalışabilir simmilar eğer bir şey bilmiyorum, ve nasıl PHP sonuçlar alacaksınız (birden fazla sorgu BİRLİĞİ olarak alınacak eğer ben bilmiyorum) ama birkaç test umut verici idi.

CREATE PROCEDURE topusers(OUT query TEXT) BEGIN
    DECLARE time TIMESTAMP;
    SELECT MIN(CONCAT(EXTRACT(YEAR_MONTH FROM FROM_UNIXTIME(p_timestamp)), '01')) INTO time FROM t;
    SET @query = '';
REPEAT
    SET @query = CONCAT(@query, '(SELECT SUM(p_points) as total_points, p_userid, ', UNIX_TIMESTAMP(time), ' 
    FROM t
    WHERE p_timestamp BETWEEN ', UNIX_TIMESTAMP(time), ' AND ', UNIX_TIMESTAMP(ADDDATE(time, INTERVAL 1 MONTH)), ' 
    GROUP BY p_userid 
    ORDER BY total_points DESC LIMIT 3)');
    SELECT ADDDATE(time, INTERVAL 1 MONTH) INTO time;
    IF time < NOW() THEN
        SET @query=CONCAT(@query, ' UNION ');
    END IF;
UNTIL time > NOW() END REPEAT;
    SELECT @query INTO query;
END//

Ve sorgu

CALL topusers(@query); PREPARE stmt1 FROM @q; EXECUTE stmt1;

ve sonunda

DEALLOCATE PREPARE stmt1;

etiketler