調剤薬局のためのデータベース講座 #6.5 在庫データ・出庫データの生成

元動画はこちら https://youtu.be/Q9J7EnkoeRg

yakkyokuテーブルの作成

CREATE TABLE `yakkyoku` (
  `pharmacy_id` int(11) PRIMARY KEY,
  `name` varchar(45) NOT NULL
);

INSERT INTO `yakkyoku` values
(1, 'とうきょう薬局'),
(2, 'ワシントン薬局'),
(3, 'ロンドン薬局'),
(4, 'パリ薬局'),
(5, 'モスクワ薬局'),
(6, 'ウィーン薬局'),
(7, 'ベルリン薬局'),
(8, 'マドリード薬局'),
(9, 'ローマ薬局'),
(10,'キャンベラ薬局');

zaiko, shukkoテーブルの作成

CREATE TABLE `shukko` (
  `date` date NOT NULL,
  `pharmacy_id` int(10) unsigned NOT NULL,
  `yj` char(12) NOT NULL,
  `amount` float NOT NULL DEFAULT '0',
  PRIMARY KEY (`date`,`pharmacy_id`,`yj`)
);

CREATE TABLE `zaiko` (
  `pharmacy_id` int(10) unsigned NOT NULL DEFAULT '0',
  `yj` char(12) NOT NULL,
  `amount` float unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`pharmacy_id`,`yj`)
);

zaiko, shukkoデータのランダム生成

set @saiyo_fact = 0.15,
    @dead_fact = 0.2,
    @shukko_fact = 0.1,
    @zaiko_base = 100,
    @shukko_base = 100,
    @date_begins = '2016-01-01';

truncate table shukko;
truncate table zaiko;
create temporary table if not exists A (n int primary key);
create temporary table if not exists B (n int primary key);
insert ignore into A values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
insert ignore into B values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

insert ignore into zaiko(
select pharmacy_id, iyakuhin.yj, floor(rand() * @zaiko_base + 1)
       FROM (iyakuhin, yakkyoku)
       left join (SELECT max(c32) as yj FROM y group by c32 having count(c3) = 1) y2 on iyakuhin.yj = y2.yj
       left join (select yj9, count(yj)  as c from iyakuhin where generic = 0 group by yj9) i2 on iyakuhin.yj9 = i2.yj9
       where generic = 0
             and y2.yj is not null
             and (rand() < @saiyo_fact)
             and ((rand() * i2.c) < 1)
       );

insert ignore into zaiko(
select pharmacy_id, iyakuhin.yj, floor(rand() * @zaiko_base + 1)
       FROM (iyakuhin, yakkyoku)
       left join (SELECT max(c32) as yj FROM y group by c32 having count(c3) = 1) y2 on iyakuhin.yj = y2.yj
       left join (select yj9, count(yj)  as c from iyakuhin where generic = 1 group by yj9) i2 on iyakuhin.yj9 = i2.yj9
       where generic = 1
             and y2.yj is not null
             and (rand() < @saiyo_fact)
             and ((rand() * i2.c) < 1)
       );

insert ignore into shukko (
select date, zaiko.pharmacy_id, zaiko.yj, ceil(rand() * 100)
       from (zaiko, (select B.n*10 + A.n as id, adddate(@date_begins, B.n*10 + A.n) as date from A, B) as dates)
       natural left join (select pharmacy_id, yj, rand() as fact from zaiko where rand() < (1 - @dead_fact)) z2
       where dates.id < (91 * fact) 
             and rand() < 0.1);
drop table A, B;