- 投稿日：2019-03-10T12:38:07+09:00
3/9
やったこと
vagrant
起動
停止
破棄
ファイル共有の確認Linux
基礎操作
webサーバー構築
PHPサーバー構築（https://learning.courage.engineer/contents/?p=608）SQLインストール
- 投稿日：2019-03-10T10:43:56+09:00
個人または家族で使える自宅用のWebポータルシステム「MyHome Portal」
PHPで作成した個人または家族で使える自宅用のWebポータルシステム「MyHome Portal」をオープンソース/フリーソフトとして公開しています。
2008年から2016年にかけて、こつこつとバージョンアップを繰り返して作成したものです。
中級プログラマの作品ですので、ソースコードはあまりきれいとは言えません。バージョンアップを繰り返したことでコードがきたなくなってしまっている部分もあります。モジュールの構造を複雑にしすぎたかもしれません。
2016/08/02以降、バージョンアップしていません。Webサービスを利用している機能など一部は、Webサービスの停止等の理由により動作しなくなっています。公開ページ
https://ok2nd.github.io/myportal/
ブログ
最近、更新はほとんどありません。
中級プログラマの自宅でPHP ブログコンセプト
- 自宅でポータルとして使えるシステム。(家庭内グループウェア)
- データはインターネット上ではなく、ローカルPCまたはローカルサーバに保管。
- 初級、中級プログラマにも理解可能なシステム。
- PHP、SQL、JavaScript、HTML、CSSの知識だけで理解可能なシステム。
- PHPの良さを生かしたHTMLインラインコーディング。(なるべくprint文を使わない。)
- ブラックボックスの少ないシステム。
- オブジェクト指向型プログラミングを使わない。手続き型(関数型)プログラミングを採用。
- MVCを使わない。
- フレームワークに頼らないシステム。
- テキストエディタ以外の開発ツールを必要としない。
対象者
- 自分または家族専用のWebポータルサイトが作りたい。
- Webポータルサイトをインターネット上ではなく、ローカルPCまたは自宅サーバーで立ち上げたい。
- 日常的に良く使う機能が、個々のアプリケーションを起動せずに、ブラウザの中で出来たらうれしい。
- スケジュールその他自分の個人情報をインターネット上に置きたくない。
- パスワードなどのID情報をインターネット上に置きたくない。
- Webプログラム開発初心者。
- プログラミングを仕事でなく、趣味でやりたい。
- オブジェクト指向が苦手。
- MVCは面倒。
- システム全体を把握したい。(ブラックボックスは少ない方が良い。)
- フレームワークを使いたくない。(PHP、SQL、JavaScriptだけで開発したい。)
機能一覧
- ホームページインデックス(ブックマーク)＆検索
- ブログパーツ貼り付け
- 付箋
- カレンダー(スケジューラ)
- 天気出現率表示
- 旅行記
- ToDo
- 掲示板
- RSSリーダー
- メモ
- フォトアルバム
- スライドショー
- 動画サムネイル
- FLV,MP4,WMV,MPG,MOV,M2TS(AVCHD)動画再生
- 学習
- 住所録
- PDF葉書宛名書き
- メール(一括受信)
- SVGお絵かき
- ペン画
- チャット(Chat)
- ID・パスワード管理
- GPSログ(GPX)ビューワ
- 預貯金管理
- 縦計だけの表計算
- HTML URL抽出&ファイルダウンロード
- テキスト縦書き表示
- Excel to グラフ
- ソースコード表示&編集＋ファイルマネージャー
- MySQL管理ツール
- ファイル暗号化/復号化
- タイマーアラート
特徴・補足
- 自宅で個人や家族で使えるシステムを目指して作りました。家庭内LANでの利用を想定しています。
- 少人数の会社のイントラでの利用も可能かもしれません。 セキュリティは万全ではないので、スケジュール共有など機能を限定して利用願います。
- ユーザー登録など、インターネット上での利用を想定した機能も付けています。ただし、セキュリティ等保障はできませんので、インターネット上での実利用は、自己責任にてお願いいたします。あくまで、LAN内またはPC単体での利用を想定しています。
- 簡単なユーザー管理機能もあります。
- 認証機能もあります。
- ユーザー単位で他のユーザーに対して参照・書込の権限設定ができます。
- レコード単位での非公開の設定もできます。
- ただし、システム管理者はデータベースを直接見ることで全ての情報を参照ができてしまいますので、悪用しないように。
- ID管理では、二重の認証が必要なしくみにしています。
- ID管理では、パスワードを独自の方式で暗号化しています。ID情報は、２つのテーブルに分割して保存します。 暗号化のキーと２つのテーブルを別々にバックアップすれば、ID情報の復元はしづらい(?)と思います。
- 簡単なログ機能も付けています。
- 簡単なシステム管理者機能も付けています。
- コンテンツを追加しやすい構造にしています。
- ページ分割有りの一覧形式のページを簡単に作れる共有ライブラリを用意しています。一覧形式での編集もできます。 /myhome/common_/include-common-mp-list.php 各コンテンツのlist.php、category.phpを参考にしてください。
- 一覧形式の表示部分のみを、独自のphpソースに置き換えられます。($mp_list_arg['template_view'])
開発環境
（Ver.3.27以降）XAMPP 1.7.1 (Windows)
- Apache 2.2.11
- MySQL 5.1.33 (Community Server)
- PHP 5.2.9 + PEA
（Ver.3.26以前）XAMPP 1.6.6a (Windows)
- Apache 2.2.8
- MySQL 5.0.51a
- PHP 5.2.5
- 投稿日：2019-03-10T02:44:42+09:00
window関数の説明
window関数の説明
概要
- 機械学習の前処理やデータ分析において便利なwindow関数の使用方法を説明する。
準備
使用するテーブル
- book(購買対象物テーブル)
- データ
book_id book_name price 1 1000のバグを持つ男 600 2 あなたもなれるスーパーエンジニア 1100 3 顧客をダマス極意 1200 4 バグか仕様か 1150 5 今こそ学ぶC++ 900 6 スーパーエンジニアを育てる方法 1100 7 上司に好かれる77の戦術 700 8 コードを書かないエンジニア 600 9 javaとjavascriptの違いを説明するには 900 10 僕は帰宅してからコーディングする 600
テーブル作成
CREATE TABLE book ( book_id SERIAL PRIMARY KEY, book_name varchar(255) not null, price DECIMAL (11, 2) not null );データインポート
INSERT INTO book (book_name, price)VALUES ('1000のバグを持つ男',600), ('あなたもなれるスーパーエンジニア',1100), ('顧客をダマス極意',1200), ('バグか仕様か',1150), ('今こそ学ぶC++',900), ('スーパーエンジニアを育てる方法',1100), ('上司に好かれる77の戦術',700), ('コードを書かないエンジニア',600), ('javaとjavascriptの違いを説明するには',900), ('帰宅後やっとコードを書き始める',600);
- purchase(購買履歴テーブル)
- データ
purchase_id user_id book_id 1 1 1 2 1 4 3 1 8 4 1 9 5 2 1 6 2 2 7 2 6 8 3 1 9 3 7 10 3 8 11 3 3 12 3 5 13 3 10
テーブル作成
CREATE TABLE purchase ( purchase_id SERIAL PRIMARY KEY, user_id INT NOT NULL, book_id INT NOT NULL, FOREIGN KEY (book_id) REFERENCES book (book_id) );データ挿入
INSERT INTO purchase (user_id, book_id)VALUES (1,1), (1,4), (1,8), (1,9), (2,1), (2,2), (2,6), (3,1), (3,7), (3,8), (3,3), (3,5), (3,10);window関数の概要
概要
- window関数は集約関数と似ているが、集約関数と異なりレコードを削除しない。
- 以下では、集約関数と比較しながらwindow関数の説明をする。
集約関数を用いた平均額の算出
- 集約関数を用いて、購入額の平均を算出する処理は以下になる。
- AVG関数の引数にpriceを指定するだけ。
- sql
SELECT AVG(price) FROM purchase INNER JOIN book USING (book_id);
- 結果
avg 819.2307692307692308
集約関数を用いたユーザーごとの平均額の算出
- 集約関数を用いて、ユーザー毎の購入額の平均を算出する処理は以下になる。
- group byを用いて、ユーザーごとにグループ化した上で平均を計算するだけ。
- sql
SELECT user_id, AVG(price) FROM purchase INNER JOIN book USING (book_id) GROUP BY user_id;
- 結果
user_id avg 3 766.6666666666666667 2 933.3333333333333333 1 812.5000000000000000
window関数を用いたユーザーごとの平均額の算出
- window関数を用いると以下のようになる。
- 集約関数と同様にユーザーごとにグループ化した上で平均が算出されるが、集約関数とは異なり、レコードが削除されない。
- sql
SELECT user_id, price, AVG (price) OVER (PARTITION BY user_id) FROM purchase INNER JOIN book USING (book_id);
- 結果
user_id price avg 1 600.00 812.5000000000000000 1 1150.00 812.5000000000000000 1 600.00 812.5000000000000000 1 900.00 812.5000000000000000 2 600.00 933.3333333333333333 2 1100.00 933.3333333333333333 2 1100.00 933.3333333333333333 3 600.00 766.6666666666666667 3 700.00 766.6666666666666667 3 600.00 766.6666666666666667 3 1200.00 766.6666666666666667 3 900.00 766.6666666666666667 3 600.00 766.6666666666666667
- 動作順序
- window関数は、JOIN, WHERE, GROUP BY と HAVING 句のあとに動作する。
- 加えて、ORDER BY句の前に動作する。
window関数の書き方
- 基本構文
window関数 (引数1, 引数2) OVER (PARTITION BY expression ORDER BY expression)
window関数
- 集約関数をwindow関数として使用できる。
- 加えて、以下のようなビルトインのwindow関数を使用できる。
- row_number(), rank(), dense_rank()
PARTITION BY
- 指定したものでパーティーションの設定(グループ化)を行う。
ORDER BY
- パーティーション内での並び方を指定する。
window関数の使用例
レコードの番号
- パーティーション内でのレコードの番号を取得する処理を以下のように書ける。
ROW_NUMBER
- ユーザーごとに購入額の高い順にレコードに番号を振る。
- ROW_NUMBER関数は、同一の値でも別の番号が振られる。
- sql
SELECT user_id, price, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY price) FROM purchase INNER JOIN book USING (book_id);
- 結果
user_id price row_number 1 600.00 1 1 600.00 2 1 900.00 3 1 1150.00 4 2 600.00 1 2 1100.00 2 2 1100.00 3 3 600.00 1 3 600.00 2 3 600.00 3 3 700.00 4 3 900.00 5 3 1200.00 6
RANK
- ROW_NUMBER関数の例と同様に、ユーザーごとに購入額の高い順にレコードに番号を振る。
- ただし同じ値があるときは同じ番号を振り、次の値はその分スキップする。
sql
SELECT user_id, price, RANK() OVER ( PARTITION BY user_id ORDER BY price) FROM purchase INNER JOIN book USING (book_id);結果
user_id price rank 1 600.00 1 1 600.00 1 1 900.00 3 1 1150.00 4 2 600.00 1 2 1100.00 2 2 1100.00 2 3 600.00 1 3 600.00 1 3 600.00 1 3 700.00 4 3 900.00 5 3 1200.00 6
DENSE_RANK
- RANK関数と似ているが、同じ値があるときに次の値をスキップしない。
sql
SELECT user_id, price, DENSE_RANK() OVER ( PARTITION BY user_id ORDER BY price) FROM purchase INNER JOIN book USING (book_id);結果
user_id price dense_rank 1 600.00 1 1 600.00 1 1 900.00 2 1 1150.00 3 2 600.00 1 2 1100.00 2 2 1100.00 2 3 600.00 1 3 600.00 1 3 600.00 1 3 700.00 2 3 900.00 3 3 1200.00 4
パーティーション内での最初と最後の値
FIRST_VALUE
- ユーザー毎の購入額の最小値をレコードに追加する。
- sql
SELECT user_id, price, FIRST_VALUE (price) OVER ( PARTITION BY user_id ORDER BY price) as user_lowest_price FROM purchase INNER JOIN book USING (book_id);
- 結果
user_id price user_lowest_price 1 600.00 600.00 1 600.00 600.00 1 900.00 600.00 1 1150.00 600.00 2 600.00 600.00 2 1100.00 600.00 2 1100.00 600.00 3 600.00 600.00 3 600.00 600.00 3 600.00 600.00 3 700.00 600.00 3 900.00 600.00 3 1200.00 600.00
LAST_VALUE
- ユーザー毎の購入額の最大値をレコードに追加する。
sql
SELECT user_id, price, LAST_VALUE (price) OVER ( PARTITION BY user_id ORDER BY price RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as user_highest_price FROM purchase INNER JOIN book USING (book_id);PARTITION BY user_id ORDER BY price RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
- パーティション全体に渡る集約処理を行うための設定
- デフォルト設定は(RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)で、これはパーティションの先頭から現在の行を集約処理の対象とする。(そのため最小値を出すときは、この指定がなくても正しく動作した)
結果
user_id price user_highest_price 1 600.00 1150.00 1 600.00 1150.00 1 900.00 1150.00 1 1150.00 1150.00 2 600.00 1100.00 2 1100.00 1100.00 2 1100.00 1100.00 3 600.00 1200.00 3 600.00 1200.00 3 600.00 1200.00 3 700.00 1200.00 3 900.00 1200.00 3 1200.00 1200.00
前後のレコード
- LAG関数やLEAD関数を使用すると、レコードの前後にあるレコードの値を取得できる。
- LAG関数やLEAD関数の書き方
LAG (expression [,offset] [,default]) LEAD (expression [,offset] [,default])
- offset
- 何行ずらした位置のレコードを取得するか
- デフォルト値： 1
- default
- ずらした位置にレコードが存在しない場合、設定する値
- デフォルト値： NULL
- LAG
- ユーザーごとに、対象のレコードの価格よりも、１つ低い価格を取得する。
sql
SELECT user_id, price, LAG(price, 1) OVER(PARTITION BY user_id ORDER BY price) AS prev_price FROM purchase INNER JOIN book USING (book_id) GROUP BY user_id, price;結果
user_id price prev_price 1 600.00 1 900.00 600.00 1 1150.00 900.00 2 600.00 2 1100.00 600.00 3 600.00 3 700.00 600.00 3 900.00 700.00 3 1200.00 900.00
LEAD
- ユーザーごとに、対象のレコードの価格よりも、１つ高い価格を取得する。
sql
SELECT user_id, price, LEAD(price, 1) OVER(PARTITION BY user_id ORDER BY price) AS next_price FROM purchase INNER JOIN book USING (book_id) GROUP BY user_id, price;結果
user_id price next_price 1 600.00 900.00 1 900.00 1150.00 1 1150.00 2 600.00 1100.00 2 1100.00 3 600.00 700.00 3 700.00 900.00 3 900.00 1200.00 3 1200.00 参考
- 以下のサイトを参考にさせていただきました(内容的にはほとんど同じですorz)