-
Notifications
You must be signed in to change notification settings - Fork 1
/
sandbox.r
141 lines (109 loc) · 4.01 KB
/
sandbox.r
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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
area <- sws_query(class.path="../sws/ojdbc14.jar", dbquery="select area, name_e from FAOSTAT.AREA ")
item <- sws_query(class.path="../sws/ojdbc14.jar", dbquery="select item, name_e from FAOSTAT.item ")
ele <- sws_query(class.path="../sws/ojdbc14.jar", dbquery="select * from FAOSTAT.ele ")
y <- FAOSTAT.TS_ICS_WORK_YR
head(x)
str(x)
y1 <- sws_query(area=33, ele=c(31, 51), item=15, year=1960:1970, class.path="../sws/ojdbc14.jar")
items <- sws_query(class.path="../sws/ojdbc14.jar", dbquery="select * from FAOSTAT.ITEM")
sws_query(class.path="../sws/ojdbc14.jar",
dbquery="select area.name_e, TS_ICS_WORK_YR.item from FAOSTAT.AREA, FAOSTAT.TS_ICS_WORK_YR
where area.area = 33 and area.area = TS_ICS_WORK_YR.area")
sws_query(class.path="../sws/ojdbc14.jar",
dbquery=
"select area.name_e, item.name_e, TS_ICS_WORK_YR.item, TS_ICS_WORK_YR.ele
from FAOSTAT.AREA, FAOSTAT.TS_ICS_WORK_YR, FAOSTAT.ITEM
where area.area = 33 and area.area = TS_ICS_WORK_YR.area
and item.item = TS_ICS_WORK_YR.item")
y <- sws_query(class.path="../sws/ojdbc14.jar", dbquery=
"SELECT
FAOSTAT.ITEM.ITEM,
FAOSTAT.ITEM.NAME_E AS ItemName,
FAOSTAT.ITEM_TYP_ELE_DISPLAY_ELE.ELE,
FAOSTAT.ITEM_TYP_ELE_DISPLAY_ELE.DISPLAY_ELE,
FAOSTAT.ITEM_TYP_ELE_DISPLAY_ELE.STD_UNIT,
FAOSTAT.ITEM_TYP_ELE_DISPLAY_ELE.LONG_NAME_E AS EleName
FROM FAOSTAT.ITEM INNER JOIN FAOSTAT.ITEM_TYP_ELE_DISPLAY_ELE ON
FAOSTAT.ITEM.ITEM_TYP = FAOSTAT.ITEM_TYP_ELE_DISPLAY_ELE.ITEM_TYP
")
# Get name of ele based on item and ele codes.
"SELECT
i.ITEM,
i.NAME_E AS ItemName,
e.ELE,
e.DISPLAY_ELE,
e.STD_UNIT,
e.LONG_NAME_E AS EleName
FROM FAOSTAT.ITEM AS i
INNER JOIN FAOSTAT.ITEM_TYP_ELE_DISPLAY_ELE AS e ON
i.ITEM_TYP = e.ITEM_TYP
"
y1 <- sws_query(ele=c(31, 51), item=15, year=1960:1980, class.path="../sws/ojdbc14.jar")
library(stringr)
library(reshape2)
y1l <- melt(y1, measure.vars=names(y1)[str_detect(names(y1), '(^num_)|(^symb_)')])
str_extract(names(y1), '([0-9]{2}$)')
x <- names(y1)[5]
orignumb <- as.numeric(str_extract(x, '([0-9]{2}$)'))
corryear <- orignumb + 1959
corrname <- str_c(str_replace(x, '(^.*)([0-9]{2}$)', '\\1'), corryear)
corrname
str_detect(c('area', 'num_01', '!symb_01'), perl('(!symb)'))
df <- data.frame(a = runif(10, 30, 40), b = rnorm(10, 35, 5))
l <- list(alessb = 'a < b', bless35 = 'b < 35')
r <- logic_check(df, l)
# Check the row has at least one error
aaply(r, .(1), function(x) sum(x) < length(x))
# What data columns take part in rules
laply(names(df), function(x) sum(str_detect(unlist(l), x)) > 0)
# Check the rule has at least one trigger
aaply(r, .(2), function(x) sum(x) < length(x))
# Mondrian
x1 <- sws_query(33:40, item=866, ele=11, year=1996:2000)
# User has to replace values represented by space with other character/string
x1$flag[x1$flag==' '] <- 'good'
write.table(x1, 'x1.tsv', quote=F, sep='\t', row.names=F)
q <-
"SELECT
i.ITEM,
i.ITEM_TYP,
i.NAME_E AS ItemName,
e.ELE,
e.DISPLAY_ELE,
e.STD_UNIT,
e.LONG_NAME_E AS EleName,
data.NUM_50,
data.AREA, area.NAME_E AS AreaName
FROM FAOSTAT.ITEM i
INNER JOIN FAOSTAT.ITEM_TYP_ELE_DISPLAY_ELE e ON
i.ITEM_TYP = e.ITEM_TYP
INNER JOIN FAOSTAT.TS_ICS_WORK_YR data ON
i.ITEM = data.ITEM and e.ELE = data.ELE
INNER JOIN FAOSTAT.AREA area ON
area.AREA = data.AREA
WHERE data.ELE=11 AND data.ITEM=866 AND area.AREA=33"
q <-
"SELECT
ITEM.ITEM,
ITEM.ITEM_TYP,
ITEM.NAME_E AS ItemName,
ITEM_TYP_ELE_DISPLAY_ELE.ELE,
ITEM_TYP_ELE_DISPLAY_ELE.DISPLAY_ELE,
ITEM_TYP_ELE_DISPLAY_ELE.STD_UNIT,
ITEM_TYP_ELE_DISPLAY_ELE.LONG_NAME_E AS EleName,
NUM_50,
TS_ICS_WORK_YR.AREA, AREA.NAME_E AS AreaName
FROM FAOSTAT.ITEM
INNER JOIN FAOSTAT.ITEM_TYP_ELE_DISPLAY_ELE ON
ITEM.ITEM_TYP = ITEM_TYP_ELE_DISPLAY_ELE.ITEM_TYP
INNER JOIN FAOSTAT.TS_ICS_WORK_YR ON
ITEM.ITEM = TS_ICS_WORK_YR.ITEM and
ITEM_TYP_ELE_DISPLAY_ELE.ELE = TS_ICS_WORK_YR.ELE
INNER JOIN FAOSTAT.AREA ON
AREA.AREA = TS_ICS_WORK_YR.AREA
WHERE TS_ICS_WORK_YR.ELE in(11) AND
TS_ICS_WORK_YR.ITEM in(866) AND AREA.AREA in(33, 79)"
y <- sws_query(dbquery=q)
# List all tables
q <- "SELECT owner, table_name
FROM all_tables"