SQL 面接の質問: 時間差の合計を求める (重複は無視)

SQL 面接の質問: 時間差の合計を求める (重複は無視)

ある会社の BI 職の面接を受けたとき、面接で SQL に関する質問がありました。一見すると非常に簡単に思えましたが、書いてみると要約が足りず、すぐに書き出すことができませんでした。

トピックは次のとおりです。

各ブランドのプロモーション日数を調べる

テーブル sale はプロモーション マーケティング テーブルです。データには重複した日付があります。たとえば、id 1 の end_date は 20180905 で、id 2 の start_date は 20180903 です。つまり、id 1 と id 2 には重複した販売日があります。各ブランドのプロモーション日数を調べます (重複はカウントされません)

表の結果は次のとおりです。

+------+-------+------------+------------+
| ID | ブランド | 開始日 | 終了日 |
+------+-------+------------+------------+
| 1 | ナイキ | 2018-09-01 | 2018-09-05 |
| 2 | ナイキ | 2018-09-03 | 2018-09-06 |
| 3 | ナイキ | 2018-09-09 | 2018-09-15 |
| 4 | oppo | 2018-08-04 | 2018-08-05 |
| 5 | oppo | 2018-08-04 | 2018-08-15 |
| 6 | 生体内 | 2018-08-15 | 2018-08-21 |
| 7 | 生体内 | 2018-09-02 | 2018-09-12 |
+------+-------+------------+------------+

最終結果は

ブランド全ての日
ナイキ13
オッポ12
生体内18

テーブルステートメントの作成

-- ----------------------------
-- テーブル構造を販売中
-- ----------------------------
`sale` が存在する場合はテーブルを削除します。
テーブル「sale」を作成します(
 `id` int(11) デフォルト NULL,
 `brand` varchar(255) デフォルト NULL,
 `start_date` 日付 デフォルト NULL、
 `end_date` 日付 デフォルト NULL
)ENGINE=InnoDB デフォルト文字セット=utf8;

-- ----------------------------
-- 販売記録
-- ----------------------------
`sale` に VALUES (1, 'nike', '2018-09-01', '2018-09-05') を挿入します。
`sale` に VALUES (2, 'nike', '2018-09-03', '2018-09-06') を挿入します。
`sale` に VALUES (3, 'nike', '2018-09-09', '2018-09-15') を挿入します。
`sale` に VALUES (4, 'oppo', '2018-08-04', '2018-08-05') を挿入します。
`sale` に VALUES (5, 'oppo', '2018-08-04', '2018-08-15') を挿入します。
`sale` に VALUES (6, 'vivo', '2018-08-15', '2018-08-21') を挿入します。
`sale` に値 (7、'vivo'、'2018-09-02'、'2018-09-12') を挿入します。

方法1:

次のレコードへの自己関連付けの方法を使用する

ブランドを選択、合計(終了日-開始日+1)、全日数から 
 (
 s.idを選択し、
  s.ブランド、
  s.開始日、
  s.end_date 、 
  if(s.start_date>=ifnull(t.end_date,s.start_date) ,s.start_date,DATE_ADD(t.end_date,interval 1 day) ) を before_date として
 sale s の左から join (select id+1 as id ,brand,end_date from sale) t on s.id = t.id and s.brand = t.brand
 s.idで並べ替え
 )tmp
 ブランド別にグループ化

運用結果

+-------+---------+
| ブランド | all_day |
+-------+---------+
| ナイキ | 13 |
| 反対 | 12 |
| 生体 | 18 |
+-------+---------+

この方法はこの質問の表には有効ですが、不連続な ID を持つブランドのレコードには適用できない可能性があります。

方法2:

SELECT a.brand,SUM(
 場合 
  a.start_date=b.start_date かつ a.end_date=b.end_date の場合
  存在しない(
  選択*
  sale c から LEFT JOIN sale d ON c.brand=d.brand 
   ここで、d.brand = a.brand
   かつ、c.start_date=a.start_date
   かつ c.id<>d.id 
   AND (d.start_date が c.start_date と c.end_date の間であり、d.end_date>c.end_date
   または 
  c.start_date が d.start_date と d.end_date の間であり、c.end_date>d.end_date である)
    ) 
   その後 (a.終了日 - a.開始日 + 1) 
  (a.id<>b.id かつ b.start_date が a.start_date かつ a.end_date かつ b.end_date>a.end_date の間) の場合、(b.end_date-a.start_date+1)
  そうでなければ0終了
  ) 毎日 
sale a から sale b を JOIN し、 a.brand=b.brand を GROUP BY a.brand にします。

運用結果

+-------+----------+
| ブランド | all_days |
+-------+----------+
| ナイキ | 13 |
| 反対 | 12 |
| 生体 | 18 |
+-------+----------+

条件の中には

d.start_date が c.start_date と c.end_date の間であり、かつ d.end_date>c.end_date である
   または 
c.start_date が d.start_date と d.end_date の間であり、c.end_date が d.end_date より大きい

置き換え可能

c.開始日 < d.終了日 かつ (c.終了日 > d.開始日)

結果も正しい

分析関数を使うことも可能です。私のパソコンにはまだOracleがインストールされていないので、MySQLで書きました。

以上がこの記事の全内容です。皆様の勉強のお役に立てれば幸いです。また、123WORDPRESS.COM を応援していただければ幸いです。

以下もご興味があるかもしれません:
  • PHPとMySqlで時間差を計算する方法の詳細な説明
  • mysql 時間差計算関数
  • PHP と MySql で時間差を計算する方法

<<:  Linux のユーザーとグループ管理によく使われるコマンドの概要

>>:  vue+canvasでタイムラインを描く方法

推薦する

MySQL スロークエリ関連パラメータの原理の分析

MySQL スロー クエリ (正式名称はスロー クエリ ログ) は、MySQL によって提供されるロ...

Dockerの匿名マウントと名前付きマウントの具体的な使用法

目次データ量匿名マウントと名前付きマウントデータボリュームの場所データ量匿名マウントと名前付きマウン...

ウェブデザイナーが持つべき資質と能力

Web デザインは、インターネットの出現後に誕生した新興の周辺産業です。 Web ページは店頭のよう...

MySQL InnoDB ストレージエンジンのメモリ管理の詳細な説明

目次ストレージエンジンのメモリ管理データ ページを LRU キューの先頭に置かないのはなぜですか?ダ...

太字の <b> と <strong> の違いの分析

私たちウェブマスターは皆、ウェブサイトを最適化する際に記事内のキーワードを太字にすることが最適化に非...

CSS変数を使用して、クールで素晴らしいフローティング効果を実現します。

最近、Grover の Web サイトで楽しいホバー アニメーションを見つけ、自分自身のインスピレー...

docker+jenkins+node.js の自動デプロイメント環境をゼロから構築する方法

このケースはCentOS 7システムに基づいていますDockerの使用経験がある人に適していますLi...

MySQLデータベースのパスワードを忘れた場合の解決策

先ほど MySQL パスワードを設定したのに、外食したり荷物を受け取ったりするときにパスワードを忘れ...

Vueでスケルトンスクリーンを実装する例

目次スケルトンスクリーンの使用Vueアーキテクチャスケルトンスクリーンアイデアの概要抽象コンポーネン...

Docker 接続 MongoDB 実装プロセスとコード例

コンテナが起動した後まず管理者にログインして新しいユーザーを作成してください $ docker ex...

Ubuntu16.04にclionをインストールするプロセス全体と手順の詳細な説明

CLion のプロセス全体を最初から説明します。CLion は、JetBrains がリリースした新...

Linux インストール Apache サーバー構成プロセス

袋を用意するインストールApacheがすでにインストールされているかどうかを確認するrpm -qa ...

CSS でフローティングにより親要素の高さが崩れる問題を解決するいくつかの方法

以前は、フロートはレイアウトによく使用されていましたが、フローティングレイアウトを使用すると親要素の...

テキストの展開と折りたたみの効果を実現するJavaScript

リスト形式のテキストの展開と折りたたみの実装は参考までに。具体的な内容は以下のとおりです。必要: 1...

Nginx をベースに特定の IP への短期アクセス数を制限する

特定の期間内に特定の IP へのアクセス回数を制限する方法は、特に悪意のある DDOS 攻撃に直面し...