日: 2019年1月31日

MySQL 8 CTE を試す

投稿日: 投稿者:

VirtualBox の CentOS7 に入れた MySQL 8 で CTE を試してみました。

あまり参考にはならず、利用効果少ないですが、

[ 利用例 ]

貼ってから気づいたのですが、プラグインの Simple Code Highlighter が対応してなく、with が太字になってないです。


with cnttbl (ld, dc, cnt)
as (
select
	d, dc, count(id) as cnt
from
	sch_export_source
group by
	ld, dc
)

select
	dc,
	pp.CD_CNTRY,
	c.CD_REGION,

	sum(Tomakomai) as Tomakomai,
	sum(Sendai) as Sendai,
	sum(Tokyo) as Tokyo,
	sum(Chiba) as Chiba,
	sum(Yokohama) as Yokohama,
	sum(Shimizu) as Shimizu,

	sum(Niigata) as Niigata,
	sum(Toyama) as Toyama,

	sum(Nagoya) as Nagoya,
	sum(Yokkaichi) as Yokkaichi,
	sum(Osaka) as Osaka,
	sum(Kobe) as Kobe,

	sum(Mizushima) as Mizushima,
	sum(Hiroshima) as Hiroshima,
	sum(Matsuyama) as Matsuyama,

	sum(Moji) as Moji,
	sum(Hakata) as Hakata,

	sum(Kagoshima) as Kagoshima,
	sum(Naha) as Naha

from
	(
	select
		dc,

		case when (ld = 'Tomakomai') then cnt else 0 end as Tomakomai,
		case when (ld = 'Sendai') then cnt else 0 end as Sendai,
		case when (ld = 'Tokyo') then cnt else 0 end as Tokyo,
		case when (ld = 'Chiba') then cnt else 0 end as Chiba,
		case when (ld = 'Yokohama') then cnt else 0 end as Yokohama,
		case when (ld = 'Shimizu') then cnt else 0 end as Shimizu,

		case when (ld = 'Niigata') then cnt else 0 end as Niigata,
		case when (ld = 'Toyama Shinko') then cnt else 0 end as Toyama,

		case when (ld = 'Nagoya') then cnt else 0 end as Nagoya,
		case when (ld = 'Yokkaichi') then cnt else 0 end as Yokkaichi,
		case when (ld = 'Osaka') then cnt else 0 end as Osaka,
		case when (ld = 'Kobe') then cnt else 0 end as Kobe,

		case when (ld = 'Mizushima') then cnt else 0 end as Mizushima,
		case when (ld = 'Hiroshima') then cnt else 0 end as Hiroshima,
		case when (ld = 'Matsuyama') then cnt else 0 end as Matsuyama,

		case when (ld = 'Moji') then cnt else 0 end as Moji,
		case when (ld = 'Hakata') then cnt else 0 end as Hakata,

		case when (ld = 'Kagoshima') then cnt else 0 end as Kagoshima,
		case when (ld = 'Naha') then cnt else 0 end as Naha

	from
		/*  この部分を CTE にしてみる */
		/*(
		select
			ld, dc, count(id) as cnt
		from
			sch_export_source
		group by
			ld, dc
		)a*/
		cnttbl a	
	) b
	left outer join mst_ppcnv pp on (b.dc = pp.NM)
	left outer join mst_cntry c on (pp.CD_CNTRY = c.CD_CNTRY) 
group by
	dc, CD_CNTRY,
	CD_REGION
order by
  CD_CNTRY, dc;

PHP GD 画像連結合成

投稿日: 投稿者:

ここで教えて頂きました : http://thr3a.hatenablog.com/entry/20181108/1541682523

[ コード例 ]


<?php

/**
 * GD 画像 連結位置方角指定連結合成
 * argv : 1:ファイル名A, 2:ファイル名B, 3:生成ファイル名, 4:追加位置 (NESW) 
 */

// 引数取得 //
$fnm_a = $argv[1];
$fnm_b = $argv[2];
$fnm_new = $argv[3];
$mrglocation = $argv[4];

// A, B画像サイズ取得 //
list($a_width, $a_height, $type_a) = getimagesize($fnm_a);
list($b_width, $b_height, $type_b) = getimagesize($fnm_b);

//echo $a_width . ":" . $a_height . "\n";
//echo $b_width . ":" . $b_height . "\n";

// 連結位置別新画像サイズ //
$total_width = 0;
$total_height = 0;
switch ($mrglocation) {
  case 'N':
    $total_height = $a_height + $b_height;
    $total_width = $a_width > $b_width ? $a_width : $b_width;
    break;
  case 'E':
    $total_width = $a_width + $b_width;
    $total_height = $a_height > $b_height ? $a_height : $b_height;
    break;
  case 'S':
    $total_height = $a_height + $b_height;
    $total_width = $a_width > $b_width ? $a_width : $b_width;
    break;
  case 'W':
    $total_width = $a_width + $b_width;
    $total_height = $a_height > $b_height ? $a_height : $b_height;
    break;
}
echo $total_width . ":" . $total_height . "\n";

// 連結サイズのキャンバス生成 //
$result_im = imagecreatetruecolor($total_width, $total_height);
// 背景を白にする //
$result_bgcol = ImageColorAllocate($result_im, 255, 255, 255);
imagefilledrectangle($result_im, 0, 0, $total_width, $total_height, $result_bgcol);

// 対象イメージ格納 //
$image_a = imagecreatefromjpeg($fnm_a);
$image_b = imagecreatefromjpeg($fnm_b);


// コピー先の画像,コピー元の画像,コピー先のx座標,コピー先のy座標,コピー元のx座標,コピー元のy座標,コピー元の幅,コピー元の高さ
//imagecopy($result_im, $image_a, 0, 0, 0, 0, $a_width, $a_height);
//
// 連結する //
switch ($mrglocation) {
  case "S":
    imagecopy($result_im, $image_a, 0, 0, 0, 0, $a_width, $a_height);
    imagecopy($result_im, $image_b, 0, $a_height, 0, 0, $b_width, $b_height);
    break;
  case "N":
    imagecopy($result_im, $image_a, 0, $b_height, 0, 0, $a_width, $a_height);
    imagecopy($result_im, $image_b, 0, 0, 0, 0, $b_width, $b_height);
    break;
  case "E":
    imagecopy($result_im, $image_a, 0, 0, 0, 0, $a_width, $a_height);
    imagecopy($result_im, $image_b, $a_width, 0, 0, 0, $b_width, $b_height);
    break;
  case "W":
    imagecopy($result_im, $image_a, $b_width, 0, 0, 0, $a_width, $a_height);
    imagecopy($result_im, $image_b, 0, 0, 0, 0, $b_width, $b_height);
    break;
}

// 新ファイルに出力 //
$filepath = pathinfo($fnm_new);
$ext = mb_strtolower($filepath['extension']);
$res = 1;
try {
  if ($ext === "jpg" || $ext === "jpeg") {
    imagejpeg($result_im, $fnm_new, 100);
  }
  else if ($ext === "png") {
    imagepng($result_im, $fnm_new, 9);
  }
  else if ($ext === "gif") {
    imagegif($result_im, $fnm_new, 100);
  }
}
catch (Exception $ex) {
  $res = 0;
}

// 解放 //
imagedestroy($result_im);

// 連想配列に格納 //
$responce = [];
$responce["Result"] = $res;
$responce["TotalW"] = $total_width;
$responce["TotalH"] = $total_height;

// JSONに変換して結果出力 //
//echo json_encode($responce, JSON_PRETTY_PRINT);
echo json_encode($responce);

?>

[ 利用画像サンプル ]

A画像

B画像

[ 連結結果 ]

大きい方の画像の余白は白にしています。大きい方の画像を先にストレッチしておくと、余白をなくせるかと思います。

S連結

W連結