Tuesday Feb 17, 2015

インフォグラフィック

日経を喫茶店で読んでいて遅まきながら知りました。

コラム :日本経済新聞

トップ > コラム > グローバル データ マップ

ほぼ毎週月曜インフォグラフィックスの面があるんですね。
日経のものと較べると寂しいものが多いですがOracleもインフォグラフィックスを作成しているので3つご紹介します。

まずは一覧です。

Infographics Library - Cloud | Oracle

Cloud - Analytics and Big Data Infographic

次はスーパーボウルに関するもの

Football Fans Revealed Infographic: What They Do, Say and Buy (DaaS (Data as a Service))

we surfaced data from the Oracle BlueKai Audience Data Marketplace and the Oracle Data as a Service Social API to reveal the profile of football fans and what they do, say and buy

3つ目が最も最近作られたインフォグラフィックスです。
Integrating Data for Maximum Performance and Minimum Costs

Wednesday Dec 10, 2014

Excel to sqlplus shell script

I wrote about copy&pasting Excel data into sqlplus running on tty before.

Copy&Paste from Excel to sqlplus. Multi-row multi-column data (INOUE Katsumi @ Tokyo)

with Excel 2007, if you copy multi-row and multi-column data from Excel,
the data is delimited by 'new-line' character and 'TAB' character.

I turned this into shell script finally. There may be no practical use but it was fun.
Here's bank statement data on Excel 2007 sheet which I want to run SELECT SQL on.


Here's one way to run. Please replace 'bashh' on top line with 'bash'. This blog server doesn't allow the 'bash' string here.

I copy&pasted from Excel to PuTTy terminal window after 2nd 'cat' line and
hit Ctrl-D.

$ cat ./heredocCTE.sh
#!/bin/bashh
read col1 col2 col3 col4
(echo "
set pagesize 50000 linesize 256
column $col1 format a20
column $col2 format a20
column $col3 format a20
column $col4 format a20
with heredoc_rows as (
select regexp_substr('" \
&&
cat \
&&
echo "','[^
]+',1,level) h_row from dual connect by level < 16),
heredoc as (
select
regexp_substr(h_row, '[^        ]+',1,1) $col1,       --  TAB char, not space
regexp_substr(h_row, '[^        ]+',1,2) $col2,
regexp_substr(h_row, '[^        ]+',1,3) $col3,
regexp_substr(h_row, '[^        ]+',1,4) $col4
from heredoc_rows where h_row is not null)
select $1 from heredoc $2
/") |
sqlplus -S scott/tiger
$ cat |./heredocCTE.sh Description,Credit,Debit 'where  Credit>100000'
vDate   Description     Debit   Credit
2003/01/24      ATM withdrawal  19000
2003/01/26      ATM withdrawal  30000
2003/01/29      wire transfer   115000
2003/01/29      wire transfer   200000
2003/01/30      ATM withdrawal  19000
2003/02/03      wire transfer   10300
2003/02/07      wire transfer   3500
2003/02/08      wire transfer   4800
2003/02/17      ATM deposit     N/A     100000
2003/02/17      wire transfer   200000
2003/02/18      ATM deposit     N/A     300000
2003/02/20      wire transfer   111000

DESCRIPTION          CREDIT               DEBIT
-------------------- -------------------- --------------------
ATM deposit          300000               N/A

I'm trying to mimic Excel analytic functions now.

About

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Categories
Archives
« September 2015
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today