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でタイムラインを描く方法

推薦する

JavaScriptのアンチシェイクとスロットリングとは

目次1. 関数デバウンス1. 画像安定化とは何ですか? 2. 関数のスロットリング2.1 タイマーの...

Nginx で WordPress を設定する方法

以前、私は自分で WordPress を構築していましたが、当時はサードパーティの仮想ホストを使用し...

ローカル yum ソースの設定、国内 yum ソースの設定、epel ソースの設定を行う Linux の手順

1. ローカルyumソースを設定する1. ISOイメージをマウントする マウント -o loop /...

Vue3 の組み合わせ API における setup、ref、reactive の完全な使用方法

1. セットアップを始める次のコード関数を簡単に紹介します。 ref 関数を使用して変数の変更を監視...

JS の効率的なマジック演算子の概要

JavaScript は現在、毎年新しいバージョンがリリースされており、より便利で効率的な新しい演算...

autoconfを使用してMakefileを生成し、プロジェクトをコンパイルする手順

序文Linux では、コンパイルとリンクには Makefile を使用する必要がありますが、適切な ...

標準的なHTMLの書き方は、Dreamweaverによって自動的に生成されるものとは異なります。

コードをコピーコードは次のとおりです。 <!--doctype はドキュメント タイプ htm...

コンテンツタイプの説明、つまりHTTPリクエストヘッダーのタイプ

コンテンツ タイプについて学ぶには、まずそれが何であるか、そして何に使用されるかを知る必要があります...

シンプルなタブバー切り替えコンテンツバーを実装するJavaScript

この記事では、タブバーの切り替えコンテンツバーを簡単に実現するためのJavaScriptの具体的なコ...

MySQL 5.7.20 のインストールと設定方法のグラフィック チュートリアル (win10)

この記事では、MySQL 5.7.20のインストールと設定方法を参考までに紹介します。具体的な内容は...

Windows システムで MySQL が起動しない場合の一般的な解決策

MySQL 起動エラーWindows 10 に MySQL をインストールする前は、net star...

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

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

Linux でファイルを削除するさまざまな方法の効率の比較

Linux で大量のファイルを削除する効率をテストします。まず500,000個のファイルを作成する$...

UniappはBaidu Voiceを使用して録音をテキストに変換する機能を実現

3日間さまざまな困難に遭遇した後、ようやくこの機能を実現しました。正常に実装できる方法を見つける前に...

CentOS 6.4 で rpm を使用して MySQL をオフラインでインストールする

rpmインストールパッケージを使用してmysqlをオフラインでインストールします。参考までに準備:公...