SQL Queries¶

Χρήσιμα SQL Queries για συχνές ενέργειες, στατιστικά και άλλα.

Στατιστικά λήξης λογαριασμών¶

SELECT COUNT(*),
       YEAR(FROM_UNIXTIME(expires_at)) AS y,
       MONTH(FROM_UNIXTIME((expires_at)) AS m
       FROM `user`
       GROUP BY y, m
       ORDER BY y, m

Στατιστικά τελευταίων συνδέσεων¶

CREATE TEMPORARY TABLE expirations AS
        SELECT user_id, MAX(`when`) AS lastlogin
               FROM user, loginout
               WHERE user.user_id = loginout.id_user
               GROUP BY user_id;
SELECT COUNT(*), YEAR(lastlogin) AS y
       FROM expirations GROUP BY y ORDER BY y;