データアナリストMysql模擬セルフテスト

I. データ作成の背景

最近mysqlを勉強しているので、その習得度を試すために、このmysqlのセルフテストの記事です。

II. データ作成テーブル構築文。

シナリオ：各ユーザーがシステムにログインすると、ログインしたipの記録、ログインした現在時刻の記録などがあり、最も重要なのはユーザーの一意の識別子であるuidである。

CREATE TABLE `user_login_time` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` varchar(255) DEFAULT NULL COMMENT 'user id',
  `curr_day` date DEFAULT NULL COMMENT 'Time of day, yyyyMMdd',
  `login_ip` varchar(32) DEFAULT '0' COMMENT 'logged in ip',
  `login_date` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'login time',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_uid` (`uid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=51505 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

第三に、SQLスクリプトを準備するための番号を作る

実環境のデータをシミュレートするために約5万件以上のエントリーが作成された

DROP PROCEDURE IF EXISTS user_login_time;--delete this procedure if it exists
DELIMITER $
CREATE PROCEDURE user_login_time()
BEGIN
    DECLARE i INT DEFAULT 1;
		
    WHILE i<=51000 DO
        INSERT INTO `user_login_time`(`uid`, `curr_day`, `login_ip`, `login_date`) VALUES (FLOOR(RAND()*50000 + 500), NOW(), "127.0.0.1", NOW());
        SET i = i+1;
    END WHILE;
END $
CALL user_login_time();

-- Counting users
select count(*) from user_login_time;

-- count how many users have logged in to this table
Answer: select count(DISTINCT uid) from user_login_time ;

-- count the total number of user logins for a given day.
select count(curr_day) from user_login_time where curr_day="2020-11-07";
select * from user_login_time where curr_day!="2020-11-07";

-- find the first user to log in on the day 2020-11-07
select uid ,login_date from user_login_time where curr_day="2020-11-07" order by login_date asc limit 1;

-- find the last user logged in on the day 2020-11-07
select uid ,login_date from user_login_time where curr_day="2020-11-07" order by login_date desc limit 1;

-- find the first ten users logged in on the day 2020-11-07
select uid ,login_date from user_login_time where curr_day="2020-11-07" order by login_date limit 10; -- error does not take into account duplicate uid's
select distinct uid,login_date from user_login_time where curr_day="2020-11-07" order by login_date limit 0,10;

-- find the last ten users logged in on the day 2020-11-07
select distinct uid ,login_date from user_login_time where curr_day="2020-11-07" order by login_date desc limit 10;--error
select distinct uid ,login_date from user_login_time where curr_day="2020-11-07" and uid is not null order by login_date desc limit 10;-- Correct.
select distinct uid ,login_date from user_login_time where curr_day="2020-11-07" and uid is not null and uid!='' order by login_date desc limit 10;-- Enhanced version to remove the empty string case

-- need to find the entire table, all users who logged in more than once; 2, 3, , 4 times are possible: examine group by 
select uid,curr_day from user_login_time group by curr_day having count(curr_day)>1; -- error
select uid,curr_day from user_login_time group by uid having count(curr_day)>1; -- coincidence
select uid,curr_day from user_login_time group by uid having count(uid)>1; -- Correct

IV. セルフテスト問題と参考解答のまとめ。

-- Counting users
select count(*) from user_login_time;

-- count how many users have logged in to this table
Answer: select count(DISTINCT uid) from user_login_time ;

-- count the total number of user logins for a given day.
select count(curr_day) from user_login_time where curr_day="2020-11-07";
select * from user_login_time where curr_day!="2020-11-07";

-- find the first user to log in on the day 2020-11-07
select uid ,login_date from user_login_time where curr_day="2020-11-07" order by login_date asc limit 1;

-- find the last user logged in on the day 2020-11-07
select uid ,login_date from user_login_time where curr_day="2020-11-07" order by login_date desc limit 1;

-- find the first ten users logged in on the day 2020-11-07
select uid ,login_date from user_login_time where curr_day="2020-11-07" order by login_date limit 10; -- error does not take into account duplicate uid's
select distinct uid,login_date from user_login_time where curr_day="2020-11-07" order by login_date limit 0,10;

-- find the last ten users logged in on the day 2020-11-07
select distinct uid ,login_date from user_login_time where curr_day="2020-11-07" order by login_date desc limit 10;--error
select distinct uid ,login_date from user_login_time where curr_day="2020-11-07" and uid is not null order by login_date desc limit 10;-- Correct.
select distinct uid ,login_date from user_login_time where curr_day="2020-11-07" and uid is not null and uid!='' order by login_date desc limit 10;-- Enhanced version to remove the empty string case

-- need to find the entire table, all users who logged in more than once; 2, 3, , 4 times are possible: examine group by 
select uid,curr_day from user_login_time group by curr_day having count(curr_day)>1; -- error
select uid,curr_day from user_login_time group by uid having count(curr_day)>1; -- coincidence
select uid,curr_day from user_login_time group by uid having count(uid)>1; -- Correct

v. エンディングです。

初めて書くので、もしこの記事に問題点を見つけたら、ご指摘ありがとうございます！感謝します。