Mysql難問の記録(スナップ)

Mysql難問の記録(スナップ)
 
エラーのタイトル:
574当選者
 
 
534.ゲームプレイ分析III(累積値を求める)
sqlアーキテクチャ

Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int)
Truncate table Activity
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5')
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-05-02', '6')
insert into Activity (player_id, device_id, event_date, games_played) values ('1', '3', '2017-06-25', '1')
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-02', '0')
insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5')

この表は、一部のゲームのプレイヤーのアクティビティを示しています.各行はプレイヤーの記録で、ある日あるデバイスを使用してログアウトする前にログインして多くのゲームをしました(0かもしれません)
質問:
SQLクエリーを作成し、各グループのプレイヤーと日付を報告し、これまでにどれだけのゲームをしたかを報告します.つまり、この日までにプレイヤーがプレイしたゲームの総数です.

Result table:
+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
| 1         | 2016-03-01 | 5                   |
| 1         | 2016-05-02 | 11                  |
| 1         | 2017-06-25 | 12                  |
| 3         | 2016-03-02 | 0                   |
| 3         | 2018-07-03 | 5                   |
+-----------+------------+---------------------+
   ID   1    ，2016-05-02     5+6=11    ，2017-06-25     5+6+1=12    。
   ID   3    ，2018-07-03     0+5=5    。
   ，      ，            。

解法1:

select t1.player_id,t1.event_date,sum(t2.games_played) games_played_so_far  
from Activity t1  inner join Activity t2
on t1.player_id = t2.player_id 
and t1.event_date >= t2.event_date
group by t1.player_id,t1.event_date
order by t1.player_id,t1.event_date

 
考え方:
私たちが求めているのは、すべてのプレイヤーが初日から最後の日まで終わることです.毎日の統計:当日のゲーム回数+それまでのすべての日のゲーム回数の合計.
まず、デカルト積を使用して、2つのテーブルを連結します.
そして、条件フィルタリング、t 1.event_date >= t2.event_date.⚠️この条件はとても重要です!それを理解すれば本題の答えが理解できる.

t1.event_date > t2.event_date:

t 1の場合.event_dateはdayone,フィルタテーブルにはt 1に関するデータが1つしかない.day_oneの.

t 1の場合.event_dateはdaytwoでは、フィルタテーブルに2行の関連t 1がある.day_twoのデータ、この2行のデータの違いはt 2である.event_date:一つはday_ワン、一つはdaytwo.

t 1の場合.event_dateはdaythree、フィルタテーブルには3行の関連t 1がある.day_threeのデータ...

同理はその後の状況を知る.

では、t 1.even_dateがグループ化されるとsum(t 2.games_played)は,当日およびその前日のプレイ回数を加算することになる.
まとめ:
一人一人が、現在と数日前にプレイしたゲームの数を加算します.
2つのテーブルの自己接続、フィルタ条件、groupby.累加和の方法で問題を解く.
別の解法:
Mysql 8を使用する.0のwindow計算関数です.

select player_id, event_date, 
    sum(games_played) over(partition by player_id order by event_date asc rows UNBOUNDED PRECEDING ) as games_played_so_far
from activity;

+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
|         1 | 2016-03-01 |                   5 |
|         1 | 2016-05-02 |                  11 |
|         1 | 2017-06-25 |                  12 |
|         3 | 2016-03-02 |                   0 |
|         3 | 2018-07-03 |                   5 |
+-----------+------------+---------------------+

 
Windowsのframe構文の使用:https://www.cnblogs.com/chentianwei/p/12145280.html
⬆️コードも使えます

range  UNBOUNDED PRECEDING

range between UNBOUNDED PRECEDING and current row

rows between UNBOUNDED PRECEDING and current row

 
⚠️range|rowsは別名の関係のようです.
より複雑な方法:
if文と@varを使用して論理判断と論理演算を行います.mysql 5には向いていません.7.分析は複雑ではありませんが、コードは複雑に書かれています.
 
 
 
626.席替え(パリティ判定)
sqlアーキテクチャ

Create table If Not Exists seat(id int, student varchar(255))
Truncate table seat
insert into seat (id, student) values ('1', 'Abbot')
insert into seat (id, student) values ('2', 'Doris')
insert into seat (id, student) values ('3', 'Emerson')
insert into seat (id, student) values ('4', 'Green')
insert into seat (id, student) values ('5', 'Jeames')

小美は中学校の情報科学技術の先生で、彼女はseat席表を持っていて、普段学生の名前と彼らに対応する席idを保存するために使っています.
縦列のidは連続的に増加する
美ちゃんは隣の二人の学生の席を変えようとした.
彼女にSQL queryを書いて美ちゃんが望んでいる結果を出力してもらえませんか?

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Abbot   |
|    2    | Doris   |
|    3    | Emerson |
|    4    | Green   |
|    5    | Jeames  |
+---------+---------+
#          ，       ：

+---------+---------+
|    id   | student |
+---------+---------+
|    1    | Doris   |
|    2    | Abbot   |
|    3    | Green   |
|    4    | Emerson |
|    5    | Jeames  | 

2つの解題方法があります:よく使われるこのような:

--       ，            
--   id   ，  id   ， ➕1    ；  id   ， -1    。
--    id，        。

select  
    (case
        when id % 2 != 0 and id  != @last_id then id + 1
        when id % 2 != 0 and id  = @last_id then id 
        else id - 1 
    end) as id,
    student
from seat,
    (select @last_id := count(1) from seat) t
order by id

ビット演算xor、または^記号を用いてビット別または演算を行う方法もあり、この式(i+1)^1-1を用いて隣接するidを交換することができる.
 
 
 
601.体育館の人の流れ
 

Create table If Not Exists stadium (id int, visit_date DATE NULL, people int)
Truncate table stadium
insert into stadium (id, visit_date, people) values ('1', '2017-01-01', '10')
insert into stadium (id, visit_date, people) values ('2', '2017-01-02', '109')
insert into stadium (id, visit_date, people) values ('3', '2017-01-03', '150')
insert into stadium (id, visit_date, people) values ('4', '2017-01-04', '99')
insert into stadium (id, visit_date, people) values ('5', '2017-01-05', '145')
insert into stadium (id, visit_date, people) values ('6', '2017-01-06', '1455')
insert into stadium (id, visit_date, people) values ('7', '2017-01-07', '199')
insert into stadium (id, visit_date, people) values ('8', '2017-01-08', '188')

 
X市には新しい体育館が建てられ、毎日の人の流量情報はこの3つの情報に記録されている:番号(id)、日付(visit_date)、人の流量(people).
クエリー文を作成して、トラフィックのピークを見つけてください.ピーク時、少なくとも連続3行の記録の中の人の流量は100以上である.
 
公式解題は3表で接続し、フィルタリングの3種類の条件を追加します.この解法は実際の応用では使用不可能であり,データ量が大きい場合には実行が遅く,ここでは大量の判断ロジックをビジネス層に配置して処理すべきである.
 
Mysql8.0ウィンドウ関数を使用できます.したがって、法則に従って書くことができます.

select id, visit_date, people 
from (
    select  id,visit_date,people,count(interval1) over(partition by interval1) as count1 
    from (
        select id,visit_date,people,id-row_number() over(order by id) as interval1  
        from stadium where people>=100
    ) t
) t1
where t1.count1 >=3

 
解析、ステップ3:

最内層使用ウィンドウ関数row_number()とidは減算され,法則が見出され,隣接するidのinterval 1の数字は同じである.

は、次いでinterval 1で

パケット化される.

最後に、連続する3つの隣接するものを選択する.

⚠️これはidが完全に隣接しているという特徴を利用しており,実際のアプリケーションでは起こり得ない.

 
 
 
585.2016年の投資(concatとgroupbyを参照)
 
sqlアーキテクチャ:

CREATE TABLE IF NOT EXISTS insurance (PID INTEGER(11), TIV_2015 NUMERIC(15,2), TIV_2016 NUMERIC(15,2), LAT NUMERIC(5,2), LON NUMERIC(5,2) )
Truncate table insurance
insert into insurance (PID, TIV_2015, TIV_2016, LAT, LON) values ('1', '10', '5', '10', '10')
insert into insurance (PID, TIV_2015, TIV_2016, LAT, LON) values ('2', '20', '20', '20', '20')
insert into insurance (PID, TIV_2015, TIV_2016, LAT, LON) values ('3', '10', '30', '20', '20')
insert into insurance (PID, TIV_2015, TIV_2016, LAT, LON) values ('4', '10', '40', '40', '40')

質問:
2016年(TIV_2016)のすべての成功した投資の金額を合わせて、2桁の小数を保留するクエリー文を書きます.
保険加入者にとって、2016年に投資に成功した条件は次のとおりです.

彼の2015年の保険額(TIV_2015)は少なくとも1人の他の保険者の2015年の保険額と同じだ.

彼の都市は他の加入者とは異なる必要があります(つまり、次元と経度は他のどの加入者とも完全に同じではありません).

ヒント:
PIDフィールドは加入者の加入番号、TIV_2015はこの加入者の2015年の総加入金額で、TIV_2016年はこの加入者の2016年の加入金額であり、LATは加入者の所在都市の次元であり、LONは加入者の所在都市の経度である.
 
 
最後の保険者のように、最初の保険者は同時に2つの条件を満たしています:1.彼の2015年の保険金額TIV_2015は'10'で、3番目と4番目の加入者の2015年の加入金額と同じです.2.彼の都市の経緯は唯一無二だ.
2番目の保険者は2つの条件を満たしていない.彼の2015年の投資TIV_2015は他の保険加入者とは違います.彼の都市の経緯は3番目の保険者と同じだ.同じ理由で、3番目の保険者の投資は失敗した.
その結果、最初の加入者と最後の加入者のTIV_2016の和、結果は45でした.
 
方法1:
groupby+havingを使用して条件に従ってフィルタします.
最初の条件に従って、該当するTIV_を見つけます.2015:

select TIV_2015 from insurance group by TIV_2015 having count(1) >= 2 

2つ目の条件に基づいて、唯一の経緯座標が見つかり、concat(lat,lon)を使用できます.
これはconcatが新しい文字列を構成するためです.
さらに⚠️groupbyは、列別にグループ化するだけでなく、expression式でグループ化することもできます.したがって、

SELECT CONCAT(LAT, LON) FROM insurance GROUP BY LAT , LON HAVING COUNT(*) = 1

しかし、合併の場合:

select sum(insurance.TIV_2016) as TIV_2016 
from insurance 
where TIV_2015 in (
    select TIV_2015 from insurance
    group by TIV_2015 having count(1) >= 2
) 
and 
concat(lat, lon) in (select concat(lat,lon) from insurance
    group by concat(lat, lon)
    having count(1) = 1
)

レポート作成❌.
 Expression #1 of HAVING clause is not in GROUP BY clause and contains nonaggregated column 'linshi.insurance.LAT' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
上の黄色い部分を書き直してconcatを取り除く必要があります.サブステートメントを単独で実行する理由が分からない

select sum(insurance.TIV_2016) as TIV_2016 
from insurance 
where TIV_2015 in (
    select TIV_2015 from insurance
    group by TIV_2015 having count(1) >= 2
) 
and 
concat(lat, lon) in (select concat(lat,lon) from insurance
    group by lat, lon
    having count(1) = 1
)

 
その他の方法:
たとえばinner joinを使用して条件をフィルタします.除外法を用いるexistを用いてもよい.