Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
game_scoreテーブルのサンプル
#!/usr/bin/perl
########################################################################
# Copyright (C) 2016 yoku0825
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
########################################################################
use strict;
use warnings;
use utf8;
use DBI;
use Time::Piece;
my @game_id= (1..10);
my @user_id= (1..10000);
### Configure me.
my $host = "localhost"; ### use "127.0.0.1" if you want to use TCP connection
my $port = 3306;
my $socket = "/var/lib/mysql/mysql.sock";
my $schema = "d1";
my $user = "root";
my $password = "";
my $row_count= 300000;
my $dsn= sprintf("dbi:mysql:%s", $schema);
$dsn .= sprintf(";host=%s", $host) if $host;
$dsn .= sprintf(";port=%d", $port) if $port;
$dsn .= sprintf(";mysql_socket=%s", $socket) if $socket;
my $now = Time::Piece::localtime;
my $conn= DBI->connect($dsn, $user, $password) or die;
my $sql = "INSERT INTO game_score (seq, game_id, user_id, play_end_time, score) VALUES (?, ?, ?, ?, ?)";
for (my $n= 1; $n <= $row_count; $n++)
{
my $real_game_id = int(rand($#game_id + 1));
my $real_user_id = int(rand($#user_id + 1));
my $real_play_end_time= $now + $n;
my $real_score = int(rand(1000000));
$conn->do($sql, undef, $n, $real_game_id, $real_user_id, $real_play_end_time->strftime("%Y/%m/%d %H:%M:%S"), $real_score);
print $n, "\n" unless $n % 5000;
}
exit 0;
=pod
mysql57> SHOW CREATE TABLE d1.game_score\G
*************************** 1. row ***************************
Table: d1.game_score
Create Table: CREATE TABLE `d1`.`game_score` (
`seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`game_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`play_end_time` datetime NOT NULL,
`score` int(11) NOT NULL,
PRIMARY KEY (`seq`),
KEY `game_id` (`game_id`),
KEY `play_end_time` (`play_end_time`)
) ENGINE=InnoDB AUTO_INCREMENT=600001 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
=cut
Owner

yoku0825 commented Dec 19, 2016

重そうなやつ

SELECT game_score.game_id, ANY_VALUE(game_score.user_id) AS user_id, ANY_VALUE(game_score.play_end_time) AS play_end_time, ANY_VALUE(score) AS score FROM game_score JOIN game_score AS dummy USING(score) GROUP BY game_score.game_id ORDER BY user_id ASC, play_end_time DESC, score ASC, user_id DESC, play_end_time ASC, score DESC, user_id ASC, play_end_time DESC LIMIT 3;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment