venerdì 7 novembre 2014
Improvement philosophy
Next time think twice about what are you doing... are you sure that you cannot stop for a second?
sabato 1 novembre 2014
Sankey Diagram with D3JS
I received a task recently which gave me a lot of fun.
I had to create a diagram like the one above with adapted numbers for my company core business.
The idea was to have such numbers in a Power point. One of the biggest challenge was to dig in the DB in order to find the correct informations (but this is beyond the scope of this post).
One of the funniest task was to build the power point presentation! How can I build a power point reporting the information such the one in the diagram above?
I googled it and I discovered that this diagram is called "Sankey" diagram.
I tried then to find any tool who was supporting the creation of those type of diagrams.
Of course, first choice was google chart:
https://developers.google.com/chart/interactive/docs/gallery/sankey
I found that this lybrary is quite static, it does not give too much freedom. Additionally the graphic is quite poor.
I searched deeper and I fell in love for the library D3js
http://d3js.org/
This library support too many of the non conventional diagrams. No words to mention, just have a look at the examples:
https://github.com/mbostock/d3/wiki/Gallery
I must confess that it is not really straightforward, not as easy as the google chart one.
As well the possibilities are enormous.
In this post I want to have my usual copy and paste snippets.
I found a very interesting tutorial here:
http://bl.ocks.org/d3noob/5028304
I have reported here the result of my task (of course number changed):
http://marcoandolfi.eu5.org/sankey_test/viewer.html
And now the copy and paste part.
You need three things:
- The javascript library
- The json file.
- The HTML file rendering it.
The javascript library Sankey.js, I stored a copy on my website here).
Here it follow an example of possible JSON file. Very easy: links and nodes.
{
"links": [
{"source":"1A","target":"2A","value":"10"},
{"source":"1A","target":"2B","value":"1" },
{"source":"1B","target":"2B","value":"15"},
{"source":"1C","target":"2A","value":"20"},
{"source":"1C","target":"2B","value":"14"},
{"source":"1C","target":"2C","value":"45"},
{"source":"1D","target":"2A","value":"25"},
{"source":"1D","target":"2C","value":"25"},
{"source":"2A","target":"3A","value":"9" },
{"source":"2A","target":"3B","value":"15"},
{"source":"2A","target":"3C","value":"28"},
{"source":"2A","target":"3D","value":"17"},
{"source":"2B","target":"3B","value":"15"},
{"source":"2B","target":"3C","value":"15"},
{"source":"2C","target":"4B","value":"50"},
{"source":"3A","target":"4A","value":"10"},
{"source":"3B","target":"4B","value":"20"},
{"source":"3B","target":"4A","value":"5" },
{"source":"3C","target":"4B","value":"40"}
] ,
"nodes": [
{"name":"1A"},
{"name":"1B"},
{"name":"1C"},
{"name":"1D"},
{"name":"2A"},
{"name":"2B"},
{"name":"2C"},
{"name":"3A"},
{"name":"3B"},
{"name":"3C"},
{"name":"3D"},
{"name":"4A"},
{"name":"4B"}
] }
Finally the HTML page (it is needed to adapt only the bold part: Json file name or ajax call and the location of your Sankey.js).
<!DOCTYPE html>
<meta charset="utf-8">
<title>SANKEY Experiment</title>
<style>
.node rect {
cursor: move;
fill-opacity: .9;
shape-rendering: crispEdges;
}
.node text {
pointer-events: none;
text-shadow: 0 1px 0 #fff;
}
.link {
fill: none;
stroke: #000;
stroke-opacity: .2;
}
.link:hover {
stroke-opacity: .5;
}
</style>
<body>
<p id="chart">
<script src="http://d3js.org/d3.v3.js"></script>
<script src="sankey.js"></script>
<script>
var units = "Widgets";
var margin = {top: 10, right: 10, bottom: 10, left: 10},
width = 1200 - margin.left - margin.right,
height = 740 - margin.top - margin.bottom;
var formatNumber = d3.format(",.0f"), // zero decimal places
format = function(d) { return formatNumber(d) + " " + units; },
color = d3.scale.category20();
// append the svg canvas to the page
var svg = d3.select("#chart").append("svg")
.attr("width", width + margin.left + margin.right)
.attr("height", height + margin.top + margin.bottom)
.append("g")
.attr("transform",
"translate(" + margin.left + "," + margin.top + ")");
// Set the sankey diagram properties
var sankey = d3.sankey()
.nodeWidth(36)
.nodePadding(10)
.size([width, height]);
var path = sankey.link();
// load the data
d3.json("data.json", function(error, graph) {
var nodeMap = {};
graph.nodes.forEach(function(x) { nodeMap[x.name] = x; });
graph.links = graph.links.map(function(x) {
return {
source: nodeMap[x.source],
target: nodeMap[x.target],
value: x.value
};
});
sankey
.nodes(graph.nodes)
.links(graph.links)
.layout(32);
// add in the links
var link = svg.append("g").selectAll(".link")
.data(graph.links)
.enter().append("path")
.attr("class", "link")
.attr("d", path)
.style("stroke-width", function(d) { return Math.max(1, d.dy); })
.sort(function(a, b) { return b.dy - a.dy; });
// add the link titles
link.append("title")
.text(function(d) {
return d.source.name + " → " +
d.target.name + "\n" + format(d.value); });
// add in the nodes
var node = svg.append("g").selectAll(".node")
.data(graph.nodes)
.enter().append("g")
.attr("class", "node")
.attr("transform", function(d) {
return "translate(" + d.x + "," + d.y + ")"; })
.call(d3.behavior.drag()
.origin(function(d) { return d; })
.on("dragstart", function() {
this.parentNode.appendChild(this); })
.on("drag", dragmove));
// add the rectangles for the nodes
node.append("rect")
.attr("height", function(d) { return d.dy; })
.attr("width", sankey.nodeWidth())
.style("fill", function(d) {
return d.color = color(d.name.replace(/ .*/, "")); })
.style("stroke", function(d) {
return d3.rgb(d.color).darker(2); })
.append("title")
.text(function(d) {
return d.name + "\n" + format(d.value); });
// add in the title for the nodes
node.append("text")
.attr("x", -6)
.attr("y", function(d) { return d.dy / 2; })
.attr("dy", ".35em")
.attr("text-anchor", "end")
.attr("transform", null)
.text(function(d) { return d.name; })
.filter(function(d) { return d.x < width / 2; })
.attr("x", 6 + sankey.nodeWidth())
.attr("text-anchor", "start");
// the function for moving the nodes
function dragmove(d) {
d3.select(this).attr("transform",
"translate(" + (
d.x = Math.max(0, Math.min(width - d.dx, d3.event.x))
) + "," + (
d.y = Math.max(0, Math.min(height - d.dy, d3.event.y))
) + ")");
sankey.relayout();
link.attr("d", path);
}
});
</script>
</body>
</html>
Most probably you want to run the first test on your machine. Therefore you need to launch your browser in a "special mode" so that it will be able to read local file (for securityy reason browser deactivate this mode). I like Chrome and therefore what follows is valid only for Chrome, but extremely similar is for the other browser.
First be sure that you have killed every process of Chrome, which does not simply mean to close the browser, but to open the task manager and kill every process.
Then create a batch file like this (or invoke from command line):
cd "C:\Program Files (x86)\Google\Chrome\Application"
chrome.exe --allow-file-access-from-files
lunedì 29 settembre 2014
Extract DB as XML
I want to have a single query which is extracting the complete structure of the database into a simple XML.
For example having:
create table my_test
(
f11 number,
f12 number,
f13 varchar2(300),
CONSTRAINT my_test_pk PRIMARY KEY (f11)
);
create table my_test_2
(
f21 number,
f22 number,
f23 varchar2(300),
CONSTRAINT my_test_pk_2 PRIMARY KEY (f21, f22),
CONSTRAINT fk_column_2
FOREIGN KEY (f22)
REFERENCES my_test (f11)
);
create table my_test_3
(
f31 number,
f32 number,
f33 varchar2(300),
CONSTRAINT my_test_pk_3 PRIMARY KEY (f31),
CONSTRAINT fk_column_3
FOREIGN KEY (f32)
REFERENCES my_test (f11)
);
comment on table my_test_2 is 'this is my test_2';
comment on table my_test is 'this is my test';
comment on column my_test.f11 is 'my_test.f11';
comment on column my_test.f12 is 'my_test.f12';
comment on column my_test.f13 is 'my_test.f13';
comment on column my_test_2.f21 is 'my_test.f21';
comment on column my_test_2.f22 is 'my_test.f22';
comment on column my_test_2.f23 is 'my_test.f23';
I want to have as output:
<?xml version="1.0" encoding="UTF-8"?>
<schema name="MARCOA">
<table name="MY_TEST" pk_column_name="F11" pk_name="MY_TEST_PK" pk_owner="MARCOA">
<comment>this is my test</comment>
<column name="F11" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">
<comment>my_test.f11</comment>
<referenced_by rk_ref_column_name="F22" rk_ref_table_name="MY_TEST_2" />
<referenced_by rk_ref_column_name="F32" rk_ref_table_name="MY_TEST_3" />
</column>
<column name="F12" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="Y">
<comment>my_test.f12</comment>
</column>
<column name="F13" num_nulls="" num_distinct="" type="VARCHAR2(300)" nullable="Y">
<comment>my_test.f13</comment>
</column>
</table>
<table name="MY_TEST_2">
<comment>this is my test_2</comment>
<column name="F21" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">
<comment>my_test.f21</comment>
</column>
<column name="F22" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">
<comment>my_test.f22</comment>
<referencing fk_ref_column_name="F11" fk_ref_table_name="MY_TEST" />
</column>
<column name="F23" num_nulls="" num_distinct="" type="VARCHAR2(300)" nullable="Y">
<comment>my_test.f23</comment>
</column>
</table>
<table name="MY_TEST_3">
<comment />
<column name="F31" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="N">
<comment />
</column>
<column name="F32" num_nulls="" num_distinct="" type="NUMBER(22, 0)" nullable="Y">
<comment />
<referencing fk_ref_column_name="F11" fk_ref_table_name="MY_TEST" />
</column>
<column name="F33" num_nulls="" num_distinct="" type="VARCHAR2(300)" nullable="Y">
<comment />
</column>
</table>
</schema>
And here is the query to do it:
with csv_constraint as (
SELECT distinct owner, constraint_name, table_name, SUBSTR (SYS_CONNECT_BY_PATH (column_name , ','), 2) csv
FROM (SELECT
acc.owner, acc.constraint_name, acc.table_name, acc.column_name,
acc.position rn,
COUNT (*) OVER (partition by cc.constraint_name) cnt
FROM
all_cons_columns acc left outer join all_constraints cc
on (acc.owner = cc.owner and acc.table_name = cc.table_name and cc.constraint_type = 'P')
where (cc.owner, cc.table_name) in (select t.owner, t.table_name from all_tables t)
/* to be changed */ and cc.owner = 'MARCOA'
)
WHERE rn = cnt
START WITH rn = 1
CONNECT BY constraint_name = prior constraint_name and rn = PRIOR rn + 1
),
xml_builder as(
select 'STARTTAG' as tagtype, '_0000' as postfix, '<?>' as xml_tag from dual union all
select 'CLOSETAG' as tagtype, '_9999' as postfix, '</?>' as xml_tag from dual
),
schemas as (
select
lpad(user_id, 4, '0') as hh_s_id,
username
from all_users
where username = user
),
tab_cols_cons as (
select
tt.owner,
tt.table_name,
cl.owner pk_owner,
cl.constraint_name pk_name,
cl.csv pk_column_name,
lpad(ss.user_id, 4, '0') as hh_s_id,
lpad(t_id, 4, '0') as hh_t_id
from (
select ROW_NUMBER( ) OVER (PARTITION BY owner ORDER BY table_name NULLS LAST) t_id, t.*
from all_tables t
)tt join all_users ss
on (tt.owner = ss.username)
left outer join csv_constraint cl
on (cl.owner = tt.owner and tt.table_name = cl.table_name)
/* to be changed */ where tt.owner = 'MARCOA' and tt.table_name like 'MY_TEST%'
),
tab_cols as (
select
atc.owner,
atc.table_name,
atc.column_name,
atc.nullable,
case
when atc.data_type = 'NUMBER' then atc.data_type || '(' || atc.data_length || ', ' || nvl(atc.data_scale, 0) || ')'
when atc.data_type = 'VARCHAR2' then atc.data_type || '(' || atc.data_length || ')'
else atc.data_type
end as col_type,
atc.num_distinct,
atc.num_nulls,
tcc.hh_s_id,
tcc.hh_t_id,
lpad(atc.column_id + 1, 4, '0') as hh_c_id
from tab_cols_cons tcc join all_tab_columns atc on (tcc.owner = atc.owner and atc.table_name = tcc.table_name)
),
refs as (
select distinct all_constraints.owner fk_owner, -- all_constraints.constraint_name, all_constraints.constraint_type,
all_constraints.constraint_name fk_name,
all_cons_columns.table_name fk_table_name,
all_cons_columns.column_name fk_column_name,
all_constraints.r_owner fk_ref_owner,
-- all_constraints.r_constraint_name fk_ref_pk_name,
fk_ref_table_name,
fk_ref_column_name
from ALL_CONSTRAINTS
join ALL_CONS_COLUMNS on ALL_CONSTRAINTS.constraint_name = ALL_CONS_COLUMNS.constraint_name
join (
select all_constraints.owner fk_owner,
all_constraints.constraint_name fk_name,
-- all_constraints.constraint_type,
all_cons_columns.table_name fk_ref_table_name,
all_cons_columns.column_name fk_ref_column_name
from ALL_CONSTRAINTS
join ALL_CONS_COLUMNS on ALL_CONSTRAINTS.constraint_name = ALL_CONS_COLUMNS.constraint_name
where constraint_type in ('P')
) on all_constraints.r_owner = fk_owner and all_constraints.r_constraint_name = fk_name
where ALL_CONSTRAINTS.owner = user
and constraint_type in ('R')
),
referenced_list as (
select fk_owner, fk_table_name rk_ref_table_name, fk_column_name rk_ref_column_name,
hh_s_id,
hh_t_id,
hh_c_id,
ROW_NUMBER( ) OVER (PARTITION BY fk_owner, fk_table_name , fk_column_name ORDER BY fk_ref_owner, fk_ref_table_name, fk_ref_column_name NULLS LAST) rd_id
from tab_cols tc join refs r on (tc.owner = r.fk_ref_owner and tc.table_name = r.fk_ref_table_name and tc.column_name = r.fk_ref_column_name )
),
referencing_list as (
select fk_ref_owner, fk_ref_table_name, fk_ref_column_name,
hh_s_id,
hh_t_id,
hh_c_id
from tab_cols tc join refs r on (tc.owner = r.fk_owner and tc.table_name = r.fk_table_name and tc.column_name = r.fk_column_name )
)
select xml
from (
select res_query, final_id, xml
from (
select
'q1' as res_query,
case
when tagtype = 'STARTTAG'
then replace(xml_builder.xml_tag, '?', 'schema name="' || username || '"')
else replace(xml_builder.xml_tag, '?', 'schema')
end as xml,
hh_s_id || xml_builder.postfix as final_id
from schemas join xml_builder on (1=1)
union all
select
'q2' as res_query,
case
when tagtype = 'STARTTAG'
then
case when pk_column_name is null
then replace(xml_builder.xml_tag, '?', 'table name="' || table_name || '"')
else replace(xml_builder.xml_tag, '?', 'table name="' || table_name || '" pk_owner = "' || pk_owner || '" pk_name = "' || pk_name || '" pk_column_name = "' || pk_column_name || '"')
end
else replace(xml_builder.xml_tag, '?', 'table')
end as xml,
hh_s_id || '_' || hh_t_id || xml_builder.postfix as final_id
from tab_cols_cons join xml_builder on (1=1)
union all
select
'q3' as res_query,
'<comment>' || comments || '</comment>' as xml,
hh_s_id || '_' || hh_t_id || '_0001' as final_id
from tab_cols_cons t join all_tab_comments atc on (t.owner = atc.owner and t.table_name = atc.table_name)
union all
select
'q4' as res_query,
case
when tagtype = 'STARTTAG'
then replace(xml_builder.xml_tag, '?', 'column name="' || column_name || '" nullable="' || nullable || '" type="' || col_type || '" num_distinct = "' || num_distinct || '" num_nulls ="' || num_nulls || '"' )
else replace(xml_builder.xml_tag, '?', 'column')
end as xml,
hh_s_id || '_' || hh_t_id || '_' || hh_c_id || xml_builder.postfix as final_id
from tab_cols join xml_builder on (1=1)
union all
select
'q5' as res_query,
'<comment>' || comments || '</comment>' as xml,
hh_s_id || '_' || hh_t_id || '_' || hh_c_id || '_0001' as final_id
from tab_cols tc join all_col_comments acc on (tc.owner = acc.owner and tc.table_name = acc.table_name and tc.column_name = acc.column_name)
union all
select
'q6' as res_query,
'<referencing fk_ref_table_name="' || fk_ref_table_name || '" fk_ref_column_name="' || fk_ref_column_name || '"/>' as xml,
hh_s_id || '_' || hh_t_id || '_' || hh_c_id || '_0002' as final_id
from referencing_list
union all
select
'q7' as res_query,
'<referenced_by rk_ref_table_name="' || rk_ref_table_name || '" rk_ref_column_name="' || rk_ref_column_name || '"/>' as xml,
hh_s_id || '_' || hh_t_id || '_' || hh_c_id || '_' || lpad(rd_id+2, 4, '0') as final_id
from referenced_list
)
order by 2
);
giovedì 25 settembre 2014
How to convert a select query into a CSV
Source:
Goal:
create table countries ( country_name varchar2 (100));
insert into countries values ('Albania');
insert into countries values ('Andorra');
insert into countries values ('Antigua');
SELECT * from countries;
COUNTRY_NAME
----------------------
Albania
Andorra
Antigua
Goal:
select * from ();
CSV
----------------------
Albania, Andorra, Antigua
Solution:
SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
COUNT (*) OVER () cnt
FROM countries)
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;
CSV
--------------------------
Albania,Andorra,Antigua
1 row selected.
martedì 29 luglio 2014
Overload
My classic approach!
You get stuck in your dozens daily problems... You do not know how to get out of it...
STOP!
This is the exact meaning of "Thinking outside the box": try to see your problem from a different angle. How can you? do not hold your problems strict in your hands... try to now look at them and come back later... The angle will be for sure different!
You get stuck in your dozens daily problems... You do not know how to get out of it...
STOP!
This is the exact meaning of "Thinking outside the box": try to see your problem from a different angle. How can you? do not hold your problems strict in your hands... try to now look at them and come back later... The angle will be for sure different!
domenica 27 luglio 2014
mercoledì 16 luglio 2014
Ajax call for Java Spring
I found a great tutorial on how to invoke an ajax call on java spring.
Source: http://crunchify.com/how-to-use-ajax-jquery-in-spring-web-mvc-jsp-example/
It follows the code (of the website reported).
This code is going to invoke an ajax call every x seconds.
Code for the controller:
package com.crunchify.controller;
import java.util.Date;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;
import java.util.Random;
/**
* @author Crunchify.com
*
*/
@Controller
public class CrunchifySpringAjaxJQuery {
@RequestMapping("/ajax")
public ModelAndView helloAjaxTest() {
return new ModelAndView("ajax", "message", "Crunchify Spring MVC with Ajax and JQuery Demo..");
}
@RequestMapping(value = "/ajaxtest", method = RequestMethod.GET)
public @ResponseBody
String getTime() {
Random rand = new Random();
float r = rand.nextFloat() * 100;
String result = "<br>Next Random # is <b>" + r + "</b>. Generated on <b>" + new Date().toString() + "</b>";
System.out.println("Debug Message from CrunchifySpringAjaxJQuery Controller.." + new Date().toString());
return result;
}
}
and here the code for the View:
<html>
<head>
<TITLE>Crunchify - Spring MVC Example with AJAX call</TITLE>
<style type="text/css">
body {
background-image:
url('http://cdn3.crunchify.com/wp-content/uploads/2013/03/Crunchify.bg_.300.png');
}
</style>
<script type="text/javascript"
src="http://code.jquery.com/jquery-1.10.1.min.js"></script>
<script type="text/javascript">
function crunchifyAjax() {
$.ajax({
url : 'ajaxtest.html',
success : function(data) {
$('#result').html(data);
}
});
}
</script>
<script type="text/javascript">
var intervalId = 0;
intervalId = setInterval(crunchifyAjax, 3000);
</script>
</head>
<body>
<div align="center">
<br> <br> ${message} <br> <br>
<div id="result"></div>
<br>
<p>
by <a href="http://crunchify.com">Crunchify.com</a>
</p>
</div>
</body>
</html>
I had to use a slight variation.
I wanted to have the ajax call only on click of a button.
The controller remains the same. What needs to be changed is the view:
<html>
<head>
<TITLE>Crunchify - Spring MVC Example with AJAX call</TITLE>
<style type="text/css">
body {
background-image:
url('http://cdn3.crunchify.com/wp-content/uploads/2013/03/Crunchify.bg_.300.png');
}
</style>
<script type="text/javascript"
src="http://code.jquery.com/jquery-1.10.1.min.js"></script>
<script type="text/javascript">
function crunchifyAjax() {
$.ajax({
url : 'ajaxtest.html',
success : function(data) {
$('#result').html(data);
}
});
}
$( "#button_refresh" ).click(function() {
crunchifyAjax();
});
</script>
</head>
<body>
<div align="center">
<br> <br> ${message} <br> <br>
<button id="button_refresh">Refresh</button>
<div id="result"></div>
<br>
<p>
by <a href="http://crunchify.com">Crunchify.com</a>
</p>
</div>
</body>
</html>
domenica 29 giugno 2014
Top 10 Stupid code
In my job, I have the opportunity of watching how people think.
I believe that a piece of code is a great demonstration about the cognitive skills of a person.
The question of the picture above says a lot... BeastUK "problem solving" skills are quite limited.
During my profession, I have to instruct developers about what is needed to do and review also their code.
Here is a short collection about the most stupid piece of code I have ever seen (believe me, it is real!).
Of course, this code has been re-adapted and made anonymous.
Anyway, I wanted to add some information about the origin of the genius, therefore you'll find below the flag of the creator's homecountry.
Some of these sniplets come from my former colleagues, freelancers, certified experts, colleagues from my customers, developers with several years of experience.
Top. 10
Ok, let's say that was a too fast copy and paste...
[…]
person.setFirstName(salutation);
person.setLastName(firstName);
person.setSalutation(lastName);
[…]
Top. 9
What about one loop and inside 5 if ?
for i in my_array loop
if (my_array(i) == 1) then
-- do something
end if;
end loop;
for i in my_array loop
if (my_array(i) == 2) then
-- do something
end if;
end loop;
for i in my_array loop
if (my_array(i) == 3) then
-- do something
end if;
end loop;
for i in my_array loop
if (my_array(i) == 4) then
-- do something
end if;
end loop;
for i in my_array loop
if (my_array(i) == 5) then
-- do something
end if;
end loop;
Top. 8
We found in the code the following select:
Difficult to understand? Why do you need a sub query? why not to put it directly in the where condition?
Since it is exactly the same result, try to read it in this form:
Unfortunately I did not have the honor to work with this genious.
I had to adapt the code in order to obfuscate confidential content of course, but the result was the same...
Given the following table:
create table myTable (
a numeric,
b numeric,
c numeric
);
We found in the code the following select:
select a
from mytable
where b = (
select b
from mytable
where c = param
)
Difficult to understand? Why do you need a sub query? why not to put it directly in the where condition?
Since it is exactly the same result, try to read it in this form:
select a
from mytable
where c = param
Top. 7
We found in the code the following select:
The second group by (the external, the surrounding one) is totally useless. It makes the sum of a single record... Let's see a simplified version...
Some confusion with the group by...
Some genious of previous masterpiece. I also in this case I have obfuscated the code.
Given the same table of before:
create table myTable (
a numeric,
b numeric,
c numeric
);
We found in the code the following select:
select a, b, sum(c)
from (
select a, b, sum(c) as c
from mytable
group by a, b
)
group by a, b
The second group by (the external, the surrounding one) is totally useless. It makes the sum of a single record... Let's see a simplified version...
select a, b, sum(c) as c
from mytable
group by a, b
Top. 6
This one left me totally attonished!!!
Found in the code:
select max(id)
into lmaxid
from log_messages;
delete from log_messages
where id <= lmaxid;
Why should
I select from the max and delete everything lower than max… therefore I delete
everything?!?!
there is
nothing behind max…
delete from log_messages;
Top. 5
HOW THE HELL A SELECT COUNT CAN RETURN NO RECORD!!!!
In any case it returns one line with a number, mostly it will be 0!!!!
Found in the code:
DECLARE
v_cnt numeric;
param numeric;
BEGIN
BEGIN
SELECT COUNT(1)
INTO v_cnt
FROM my_table
WHERE field_1 = param;
EXCEPTION
WHEN NO_DATA_FOUND THEN
//do something else
END;
END;
Top. 4
What does it do the NVL function in Oracle?
Let's say:
NVL(a,b)
- If a is not null then it returns the value of a.
- If the a is a NULL value then it returns the value of b.
Tipically used in select statement in order to overwrite possible NULL values coming from the table.
Given the same table of before:
create table myTable (
a numeric,
b numeric,
c numeric
);
Found in the code:
select
nvl('a', a),
nvl('b', b),
nvl('c', c)
from dual;
How can it be possible that a constant value (like 'a') can be NULL?
Is it now more clear?
select
'a', 'b', 'c'
from dual;
Top. 3
How to make your life more complicated...
Look at this piece of code and try to understand what is doing.
select
case when count(*) > 0
then count(*)
else 0
end
from table
I am kind of sure you still do not get it... Try to see the version below, it is absolutely equivalent:
select
count(*)
from table
Top. 2 (Log, part 1)
Instructions about task:
Me: Hi Dev, I saw that you are using static code. Actually for this project has been decided to use dynamic code. In particular we need to store the EXACT query that has been executed! Including the parameters used in queries.
Therefore we make the query dynamic into a variable, save the variable in the log table and then execute the variable.
Dev: Ok, all clear, now make sense! So store the query in the log!
Expectation:
procedure myProcedure() begin
[...]
v_sql := 'insert into myTable
select * from mySource
where parameter = ' || var;
insert_in_log(v_sql);
execute v_sql;
[...]
end;
Here is what I got:
procedure myProcedure() begin
[...]
insert into myTable
select * from mySource
where parameter = var;
v_sql := 'insert into myTable
select * from mySource
where parameter = <<parameter>>';
insert_in_log(v_sql);
[...]
end;
Me: Dev.... you are still using static code
Dev: Yes, but the query is in the log!
Me: But not EXACTLY what has been executed... where is the parameter?
Dev: here look, where it is written <<parameter>>
Top. 1 (Log, part 2)
Instructions about task:
Me: Hi Dev, as you know we are using the severity of the log entries. We noticed that your code is writing too many entries at the same level. It does not matter if we use a log level as "debug" or as "production", it writes anyway too many entries. Can you reduce the number of entries in the log? Just assign properlyy the log severity.
Dev: Ok, all clear, reduce the number of entries in the log
Expectation (watch out the severity):
procedure myProcedure() begin
[...]
query 1
insert_in_log(query, high_severity);
query 2
insert_in_log(query, medium_severity);
query 3
insert_in_log(query, high_severity);
query 4
insert_in_log(query, low_severity);
end;
Here is what I got (watch out the severity and the final delete):
procedure myProcedure() begin
[...]
query 1
insert_in_log(query, high_severity);
query 2
insert_in_log(query, high_severity);
query 3
insert_in_log(query, high_severity);
query 4
insert_in_log(query, high_severity);
delete * from log;
[...]
end;
Me: Dev.... ehm... why do you delete the entries from the log?
Dev: Com'on... you told me you wanted less entries in the log!
Theory of Stupidy
The pleasure of working with smart colleagues is wonderful.
Nothing pays more (professionally speaking) than the moment in which you manage to perform a good job, feeling a great synchrony with a smart colleague.
The moment in which you notice that it is enough to exchange two sentences in order to comunicate a very complicated concept and the security of being understood.
The possibility of meeting a smart colleague who enrich you can be very hard.
On the contrary, the possibility of meeting a dumb colleague may be very high! The colleague making his own life harder without any (logical) reason, the colleague who generates that piece of code that you will admire for ever.
We always have to deal with stupidity, in our every day life.
Is there any tool which can help us? Well, some years ago I read an inspiring book!
"Allegro ma non troppo" by Carlo M. Cipolla.
The book is about a scientific analysis of the human stupidity. It is proposed an illuminating mathematical model and 5 theorems.
If anybody out there is reading this blog, you know that this blog is meant to be short! I am therefore proposing here just the laws and the model. If you will be touched by such illumination, then I suggest you to read the book.
For the pictures I thank this website:
http://nicholasbordas.com/archives_posts/what-if-we-didnt-underestimate-stupidity
venerdì 20 giugno 2014
Project management triangle
The basic rule of every project!
"If you move one of the vertex, be ready to move also the others!".
If you want to have more scope (more features or more quality), then be ready to require more time.
If you want to reduce the time, then be ready to increase the cost.
If you do not want to reduce your cost, then give up with your feature.
"If you move one of the vertex, be ready to move also the others!".
If you want to have more scope (more features or more quality), then be ready to require more time.
If you want to reduce the time, then be ready to increase the cost.
If you do not want to reduce your cost, then give up with your feature.
giovedì 19 giugno 2014
Dale Carnegie
I have just read a very interesting book:
"How to Win Friends and Influence People" of Dale Carnegie.
I have to say it is a great book! I have been really hit very hard inside.
Nice part of this book is that it has a very short sentence with a great recap power.
I am currently reading another of his book: "How to stop worring and start living".
I am posting now his "Golden Rules" in order to have a quick reference:
Become a Friendlier Person
1. Don’t criticize, condemn or complain.
2. Give honest, sincere appreciation.
3. Arouse in the other person an eager want.
4. Become genuinely interested in other people.
5. Smile.
6. Remember that a person’s name is to that person the sweetest and most important sound in any language.
7. Be a good listener. Encourage others to talk about themselves.
8. Talk in terms of the other person’s interests.
9. Make the other person feel important - and do it sincerely.
Win People to Your Way of Thinking
10. The only way to get the best of an argument is to avoid it.
11. Show respect for the other person’s opinion. Never say, “You’re wrong.”
12. If you are wrong, admit it quickly and emphatically.
13. Begin in a friendly way.
14. Get the other person saying “yes, yes” immediately.
15. Let the other person do a great deal of the talking.
16. Let the other person feel that the idea is his or hers.
17. Try honestly to see things from the other person’s point of view.
18. Be sympathetic with the other person’s ideas and desires.
19. Appeal to the nobler motives.
20. Dramatize your ideas.
21. Throw down a challenge.
Be a Leader
22. Begin with praise and honest appreciation.
23. Call attention to people’s mistakes indirectly.
24. Talk about your own mistakes before criticizing the other person.
25. Ask questions instead of giving direct orders.
26. Let the other person save face.
27. Praise the slightest improvement and praise every improvement. Be “hearty in your
approbation and lavish in your praise.”
28. Give the other person a fine reputation to live up to.
29. Use encouragement. Make the fault seem easy to correct.
30. Make the other person happy about doing the thing you suggest
Fundamental Principles for Overcoming Worry
1. Live in “day tight compartments.”
2. How to face trouble:
a. Ask yourself, “What is the worst that can possibly happen?”
b. Prepare to accept the worst.
c. Try to improve on the worst.
3. Remind yourself of the exorbitant price you can pay for worry in terms of your health.
Basic Techniques in Analyzing Worry
1. Get all the facts.
2. Weigh all the facts — then come to a decision.
3. Once a decision is reached, act!
4. Write out and answer the following questions:
a. What is the problem?
b. What are the causes of the problem?
c. What are the possible solutions?
d. What is the best possible solution?
Break the Worry Habit Before It Breaks You
1. Keep busy.
2. Don’t fuss about trifles.
3. Use the law of averages to outlaw your worries.
4. Cooperate with the inevitable.
5. Decide just how much anxiety a thing may be worth and refuse to give it more.
6. Don’t worry about the past.
Cultivate a Mental Attitude that will Bring You Peace and Happiness
1. Fill your mind with thoughts of peace, courage, health and hope.
2. Never try to get even with your enemies.
3. Expect ingratitude.
4. Count your blessings — not your troubles.
5. Do not imitate others.
6. Try to profit from your losses.
7. Create happiness for others.
domenica 27 aprile 2014
Basic SQL Operation in R
I want to have in R the equivalent of most of the basic operations normally performed in SQL.
In this post it will follow a sniplet in SQL and immediately after the correspondent in R.
Topics Covered:
- Distinct
- Where
- Inner / outer joins
- Group by
Before starting with the Pure R syntax, just keep in mind that R is providing a very useful package called SQLDF. Through this package it is possible to perform a simple SQL query over tables / data frames.
# installs everything you need to use sqldf with SQLite
# including SQLite itself
install.packages("sqldf")
# shows built in data frames
data()
# load sqldf into workspace
library(sqldf)
sqldf("select * from iris limit 5")
sqldf("select count(*) from iris")
sqldf("select Species, count(*) from iris group by Species")
# create a data frame
DF <- data.frame(a = 1:5, b = letters[1:5])
sqldf("select * from DF")
sqldf("select avg(a) mean, variance(a) var from DF") # see example 15
Source: http://code.google.com/p/sqldf/
WHERE
SELECT *
FROM df1
WHERE product = "Toaster"
In R:
df1 = data.frame(CustomerId=c(1:6),Product=c(rep("Toaster",3),rep("Radio",3))) ;
df <- df1[df1$Product=="Toaster",];
DISTINCT
the select distinct in SQL: select distinct x
from my_table;
The equivalent in R is:
> x <- list(a=c(1,2,3), b = c(2,3,4), c=c(4,5,6))
> xx <- unlist(x)
> xx
a1 a2 a3 b1 b2 b3 c1 c2 c3
1 2 3 2 3 4 4 5 6
> unique(xx)
[1] 1 2 3 4 5 6
INNER / OUTER JOINS
Having in SQL the following query: select *
from product [left] [right] [outer] join countries
on (product.customer_id = countries.customer_id)
In R:
df1 = data.frame(CustomerId=c(1:6),Product=c(rep("Toaster",3),rep("Radio",3)))
df2 = data.frame(CustomerId=c(2,4,6),State=c(rep("Alabama",2),rep("Ohio",1)))
> df1
CustomerId Product
1 Toaster
2 Toaster
3 Toaster
4 Radio
5 Radio
6 Radio
> df2
CustomerId State
2 Alabama
4 Alabama
6 Ohio
#Outer join:
merge(x = df1, y = df2, by = "CustomerId", all = TRUE)
#Left outer:
merge(x = df1, y = df2, by = "CustomerId", all.x=TRUE)
#Right outer:
merge(x = df1, y = df2, by = "CustomerId", all.y=TRUE)
#Cross join:
merge(x = df1, y = df2, by = NULL)
Source:
http://stackoverflow.com/questions/1299871/how-to-join-data-frames-in-r-inner-outer-left-right
GROUP BY
For the Group By function there are many options.
Let's start with the most basic one:
Having in SQL the following snipplet:
CREATE TABLE my_table (
a varchar2(10 char),
b varchar2(10 char),
c number
);
SELECT a, b, mean(c)
FROM my_table
GROUP BY a, b
In R:
grouped_data <- aggregate(my_table, by=list(my_table$a, my_table$b, FUN=mean);
Alternatively:
> mydf
A B
1 1 2
2 1 3
3 2 3
4 3 5
5 3 6
> aggregate(B ~ A, mydf, sum)
A B
1 1 5
2 2 3
3 3 11
If your data are large, I would also recommend looking into the "data.table" package.
> library(data.table)
> DT <- data.table(mydf)
> DT[, sum(B), by = A]
A V1
1: 1 5
2: 2 3
3: 3 11
And finally the most recommended ddply function:
> DF <- data.frame(A = c("1", "1", "2", "3", "3"), B = c(2, 3, 3, 5, 6))
> library(plyr)
> DF.sum <- ddply(DF, c("A"), summarize, B = sum(B))
> DF.sum
A B
1 1 5
2 2 3
3 3 11
Source:
http://stackoverflow.com/questions/18799901/data-frame-group-by-column
venerdì 25 aprile 2014
Boss Vs. Leader
I think it is a bit old, but I would like to have it stamped it on my blog...
I do not have so much time these days :/ this is the most I can do...
I do not have so much time these days :/ this is the most I can do...
domenica 13 aprile 2014
ORACLE: Analytical Functions
The concept of analytical query is something that can highly speed up the development and the execution of your queries.
In particular because they are automatically optimized by oracle itself.
Here there are reported in a veeeeery small nutshell:
Count (member of elements in the same group)
SELECT empno, deptno,
COUNT(*) OVER (PARTITION BY
deptno) DEPT_COUNT
FROM emp
WHERE deptno IN (20, 30);
EMPNO DEPTNO DEPT_COUNT
---------- ---------- ----------
7369 20 5
7566 20 5
7788 20 5
7902 20 5
7876 20 5
7499 30 6
7900 30 6
7844 30 6
7698 30 6
7654 30 6
7521 30 6
11 rows selected.
Row Number (id of the entry within the group)
SELECT empno, deptno, hiredate, ROW_NUMBER( ) OVER (PARTITION BY deptno ORDER BY hiredate NULLS LAST) SRLNO FROM emp WHERE deptno IN (10, 20) ORDER BY deptno, SRLNO; EMPNO DEPTNO HIREDATE SRLNO ------ ------- --------- ---------- 7782 10 09-JUN-81 1 7839 10 17-NOV-81 2 7934 10 23-JAN-82 3 7369 20 17-DEC-80 1 7566 20 02-APR-81 2 7902 20 03-DEC-81 3 7788 20 09-DEC-82 4 7876 20 12-JAN-83 5 8 rows selected.
Rank & Dense Rank (member of elements in the same group)
SELECT empno, deptno, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) RANK, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC NULLS LAST) DENSE_RANK FROM emp WHERE deptno IN (10, 20) ORDER BY 2, RANK; EMPNO DEPTNO SAL RANK DENSE_RANK ------ ------- ----- ----- ---------- 7839 10 5000 1 1 7782 10 2450 2 2 7934 10 1300 3 3 7788 20 3000 1 1 7902 20 3000 1 1 7566 20 2975 3 2 7876 20 1100 4 3 7369 20 800 5 4 8 rows selected.
Lead & Lag (next / previous member of the group respect the current element)
SELECT deptno, empno, sal,
LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) NEXT_LOWER_SAL,
LAG(sal, 1, 0) OVER (PARTITION BY dept ORDER BY sal DESC NULLS LAST) PREV_HIGHER_SAL
FROM emp
WHERE deptno IN (10, 20)
ORDER BY deptno, sal DESC;
DEPTNO EMPNO SAL NEXT_LOWER_SAL PREV_HIGHER_SAL
------- ------ ----- -------------- ---------------
10 7839 5000 2450 0
10 7782 2450 1300 5000
10 7934 1300 0 2450
20 7788 3000 3000 0
20 7902 3000 2975 3000
20 7566 2975 1100 3000
20 7876 1100 800 2975
20 7369 800 0 1100
8 rows selected.
First Value & Last Value
-- How many days after the first hire of each department were the next
-- employees hired?
SELECT empno, deptno, hiredate ? FIRST_VALUE(hiredate)
OVER (PARTITION BY deptno ORDER BY hiredate) DAY_GAP
FROM emp
WHERE deptno IN (20, 30)
ORDER BY deptno, DAY_GAP;
EMPNO DEPTNO DAY_GAP
---------- ---------- ----------
7369 20 0
7566 20 106
7902 20 351
7788 20 722
7876 20 756
7499 30 0
7521 30 2
7698 30 70
7844 30 200
7654 30 220
7900 30 286
11 rows selected.
Source:
http://www.orafaq.com/node/55
mercoledì 9 aprile 2014
File system access on Oracle
It may sound easy, but accessing the file system from oracle can be painful.
I am not talking about read / write a file. I am talking about making a ls or dir command, crete folders, move files, etc.
In this post I would like to recall an easy system about making ls.
Actually the solution is already very well explained in this web page:
http://plsqlexecoscomm.sourceforge.net/
The solution is mainly based on a java package installed in the Oracle DB, which is accessing the file system and arranging the data in a proper way.
First of all it is needed to install the package (available on the link above) and then perform a simple query like the one below:
select *
from table(
file_pkg.get_file_list(file_pkg.get_file('/'))
)
And here you are: you get the result of a ls command executed on the root accessible as a simple select.
domenica 30 marzo 2014
SAP HANA: exception Handling
I think that the exception handling in SAP Hana is a bit "non-intuitive" (to be nice…).
I invest a bit of time in order to understand how does it exactly works.
I prepared a small tutorial and summerized here below.
Theory:
In java
public class myClass() {
...
public void myMethod() {
...
try {
//code to be executed causing exception
}
catch(Exception e) {
//log the exception
}
...
}
...
}
Equivalent in SQLScript
CREATE PROCEDURE myproc AS
BEGIN
...
DECLARE EXIT HANDLER FOR SQL_ERROR_CODE MY_SQL_ERROR_CODE
BEGIN
-- log the exception
END;
-- code to be executed causing exception
end;
Practical example
copy and paste on SAP HANA Studio. First prepare something:
CREATE TABLE MYTAB (I INTEGER PRIMARY KEY);
drop PROCEDURE myproc;
Then create this procedure:
CREATE PROCEDURE myproc AS
BEGIN
declare myvar int;
DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 1299
BEGIN
write_debug_log('MARCO TEST',
'Handler of the NO_DATA_FOUND exception of the select below (after the following begin end block)',
'SQL_ERROR_CODE = ' || ::SQL_ERROR_CODE || '; SQL_ERROR_MESSAGE = ' || ::SQL_ERROR_MESSAGE);
END;
begin
declare my_test int;
--CASE GENERIC FOR ANY POSSIBLE EXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION
begin
write_debug_log('MARCO TEST',
'separate handler for division by 0',
'SQL_ERROR_CODE = ' || ::SQL_ERROR_CODE || '; SQL_ERROR_MESSAGE = ' || ::SQL_ERROR_MESSAGE);
end;
my_test := 1/0;
end;
SELECT I INTO myVar FROM MYTAB; --NO_DATA_FOUND exception
SELECT 'NeverReached_noContinueOnErrorSemantics' FROM DUMMY;
END;
Please, notice that I am using my classing logging procedure.
Just substitute this procedure with any other suitable to your environment for logging...
Then let it run:
--execute the code and raise the exceptions
call myproc;
--check the situation in the log table
select * from log
order by id desc;
Again, here is the code for accessing my log table...
Just use the one of your environment.
Just use the one of your environment.
sabato 15 marzo 2014
Octave Cheat Sheet
Octave Cheat Sheet:
I found this very interesting cheat sheet about Octave here:
Source http://altons.github.io/octave/2013/05/05/octave-commands-cheat-sheet/
I think it is very great, but there are couple of commands missing (like PS1, which in my opinion makes your life better!).
So I have taken it and extendend with some commands.
I found this very interesting cheat sheet about Octave here:
Source http://altons.github.io/octave/2013/05/05/octave-commands-cheat-sheet/
I think it is very great, but there are couple of commands missing (like PS1, which in my opinion makes your life better!).
So I have taken it and extendend with some commands.
Management & Help
Task
|
Command
|
exits software
|
quit or exit
|
list variables in session
|
who
|
list variables in session with info about type
|
whos
|
deallocate variable(s)
|
clear varname
|
displays search path
|
path
|
Adds path to search
|
addpath
|
clear screen
|
clc
|
list .m files in dir
|
what
|
search for keyword
|
lookfor
|
displays help for function
|
help funname
|
Parse and execute the contents of FILE
|
source(".octaverc")
|
List installed packages
|
pkg list
|
Install packages
|
pkg install [-auto or -noauto] pkg_name.tar.gz
|
Describe packages
|
pkg describe pkg_name
|
Load/Unload package
|
pkg load/unload pkg_name
|
Uninstall package
|
pkg uninstall pkg_name
|
Shell Commands
Task
|
Command
|
Change Linde starter
|
PS1(‘desired Starter’); %PS1(‘>> ‘);
|
change working directory to dir
|
cd dirname
|
print working directory
|
Pwd
|
print directory listing
|
ls
|
return value of named environment variable
|
getenv (string)
|
execute arbitrary shell command string
|
system (cmd)
|
Load the file
You will load a file and make it available with a
Varible having same name of the file without extension
|
Load FILENAME_IN_PWD
Load(‘FILENAME_IN_PWD’);
|
Save variable into file
You will load a file and make it available with a
Varible having same name of the file without extension
|
save FILENAME_IN_PWD
VARIABLE_NAME;
|
Special Operators
Definition
|
Operator
|
Example
|
comment
|
%
|
% Initialising
|
wildcard
|
*
|
clear p*
|
array
|
[ ]
|
[1 2; 3 4]
|
Cell Arrays
|
{ }
|
p = { 10 , 15 , "rectangle" }; p{1} =10
|
ranges
|
start:step:stop (default step=1)
|
1:10 or 1:10:100
|
variable assignment
|
=
|
A=magic(3);
|
do not display
|
;
|
a=1;
|
Workflow
Task
|
Command
|
Suspend the execution of the program.
|
pause; or pause(5)
|
print string to the stout
|
fprintf("Hello World")
|
Vectors & Matrices
Rules
· Square brackets delimit literal matrices.
· Commas separate elements on the same row.
· Semicolons separate rows.
· Commas may be replaced by spaces, and
· semicolons may be replaced by one or more newlines.
· Elements of a matrix may be arbitrary expressions, provided that all the dimensions agree.
· Optional: Matrices are denoted by uppercase letters A,B,X etc while vectors by lowercase letters a,b,y etc.
· Commas separate elements on the same row.
· Semicolons separate rows.
· Commas may be replaced by spaces, and
· semicolons may be replaced by one or more newlines.
· Elements of a matrix may be arbitrary expressions, provided that all the dimensions agree.
· Optional: Matrices are denoted by uppercase letters A,B,X etc while vectors by lowercase letters a,b,y etc.
Example
|
Expression
|
enter a row vector
|
[ x, y, ... ]
|
enter a column vector
|
[ x; y; ... ]
|
enter a 2×2 matrix
|
[ w, x; y, z ]
|
Return a row vector with N linearly spaced elements between BASE and
LIMIT
|
linspace (BASE, LIMIT, N)
|
Similar to linspace except that the values are logarithmically
spaced
|
logspace(BASE, LIMIT, N)
|
Higher Dimensional Arrays
|
B(z,y,z)=1
|
Sorting arrays
|
sort(A); or sort(A, 'descend');
|
Return true if any element in array is non-zero
|
any(A)
|
Return true if all element are non-zero
|
all(A)
|
Return a vector of indices of a matrix
|
[i , j] = find(A<0.5)
|
Array Operations
Task
|
Function
|
select elements of a vector
|
A(i)
|
select elements of a matrix
|
A(i,j)
|
select rows (columns) corresponding to the elements of a range
|
A(1:2,1); A(1:2,1:5);
|
select all rows (columns)
|
A(:,1); A(3,:);
|
Delete a row/column of a matrix
|
A(2,:)= [] or A(:,5) = []
|
Linear Algebra
Task
|
Function
|
dimensions of the array
|
size
|
returns larger dimension
|
length
|
allocates array with 0’s
|
zeros
|
allocates array with 1’s
|
ones
|
transpose array
|
'
|
Compute the determinant of A.
|
det(A)
|
inverse of matrix
|
inv
|
pseudo inverse of matrix
|
pinv
|
Calculate Eigenvalues / Eigenvectors of matrix A
|
eig(A) or [V,L] = eig(A)
|
Identity matrix
|
eye
|
Compute the rank of a Matrix
|
rank(A)
|
returns the lower triangular part of A
|
tril(A)
|
returns the upper triangular part of A
|
triu(A)
|
Create an N-by-N magic square
|
magic
|
Compute the dot product of two vectors
|
dot(A,B)
|
Compute the vector cross product of two 3-dimensional vectors X and Y
|
cross(X,Y)
|
interval range arrays
|
v = 1:0.1:2
%vector = STARTING_AT:DELTA:ENDING_AT
|
Plotting
|
Task
|
Command
|
Example
|
|
2-D plot
|
plot
|
plot(x,y); plot(x, a+b+c);
|
|
Surface plot
|
surf
|
surf(theta0, theta1, J);
|
|
Contour plot
|
contour
|
contour(theta0, theta1, J, logspace(-2, 3, 20))
|
|
Specify x-, y-, or z-axis labels for the current axis
|
[x,y,z]label
|
xlabel('k lags')
|
|
Set a plot window to plot window N
|
figure
|
figure;plot(x, a);
|
|
close figure window(s)
|
close
|
close [***(N),all,all hidden***]
|
|
new graphic objects are added to the plot
|
hold
|
hold on;plot(x, b);
|
|
Clear plot and restore default graphics settings
|
hold
|
hold off;
|
|
Display a legend for the axes
|
legend
|
plot(X,Y);legend('Temperature', 'Gas Demand');
|
|
give a title
|
title
|
title('myTitle');
|
|
export the chart
|
print
|
Print –dpng ‘filename.png’
|
Math Functions
Type
|
Function
|
Examples
|
Sum of elements along dimension DIM
|
sum
|
sum([1 2 3 4])
|
Product of elements along dimension DIM
|
prod
|
prod([1 2 3 4)]
|
Trigonometric
|
sin, cos, tan
|
floor((1+tan(1.2)) / 1.2)
|
Inverse Trigonometric
|
asin, acos, atan
|
|
Natural Logarithm
|
log
|
|
Base 10 Logarithm
|
log10
|
log10(100)/log10(10)
|
Exponentiation
|
exp
|
exp(0)
|
Absolute value
|
abs
|
abs(-3)
|
Square Root
|
sqrt
|
sqrt(3^2 + 4^2)
|
X raised to the Y power
|
power(X,Y)
|
power(3,2)
|
Real part of complex number
|
real
|
real(3+5I)
|
Imaginary part of complex number
|
imag
|
imag(3+5I)
|
Evaluate polynomials
|
polyval
|
polyval([2 10.1 0 6],0)
|
Write formatted polynomial
|
polyout
|
polyout([2 -3 1],"x")
|
Return the largest integer not greater than X
|
floor
|
floor(1.9)
|
Return the smallest integer not less than X
|
ceil
|
ceil(3.7)
|
Return the integer nearest to X
|
round
|
round(1.9)
|
Truncate fractional portion of X and return the integer portion
|
fix
|
fix(pi)
|
Stats Functions
Task
|
Example
|
Function
|
Uniform random numbers btw 0 and 1 (both excluded)
|
rand
|
rand(3,5)
|
Normal(0,1) random numbers
|
randn
|
randn(1,3)
|
Gamma distribution
|
randg
|
randg
|
Exponential distribution
|
rande
|
rande(1,10)
|
Poisson distribution
|
randp
|
randp(0.5,3,3)
|
Min value by column
|
min
|
min(A)
|
Max value by column
|
max
|
max(A)
|
Constants
Name
|
Expression
|
Default Variable
|
ans
|
Pi
|
pi
|
Euler's number
|
e
|
Imaginary number
|
i, j and I
|
Infinity
|
inf
|
Not a Number
|
NaN
|
machine precision
|
eps
|
true/false
|
logical 1/0
|
Logical Operators
Expression
|
Operator
|
is greater than
|
>
|
is less than
|
<
|
is greater than or equal to
|
>=
|
is less than or equal to
|
<=
|
is equal to
|
==
|
is not equal to
|
∼= or !=
|
AND with short circuiting
|
&&
|
with short circuiting
|
||
|
AND
|
&
|
OR
|
|
|
NOT
|
∼
|
Auxiliary Functions
Task
|
Function
|
Check a scalar
|
isscalar(a)
|
Check a vector
|
isvector(b)
|
Check a matrix
|
ismatrix(b)
|
is func available
|
is TAB TAB
|
Type info
|
typeinfo(b)
|
String Functions
Task
|
Function
|
Example
|
Compare 2 strings
|
strcmp
|
strcmp("hello","Hello")
|
Import & Export Data
Task
|
Command
|
Example
|
Read the matrix DATA from a text file
|
dlmread (FILE, SEP, R0, C0)
|
dmlread("virus.dat",",",1,1);
|
Write data to a text file
|
dlmwrite (FILE, M, DELIM, R, C)
|
dlmwrite("out.txt",yhat,";",1,1);
|
Read the csv files
|
csvread (FILENAME, DLM_OPTS)
|
csvread("input.csv");
|
Write csv files
|
csvwrite (FILENAME, X, DLM_OPTS)
|
csvwrite("output.csv", yhat);
|
Defining Functions
Simplest Form
function name
body
end
Example:
function wakeup
printf ("\a");
end
Passing Params
function name (arg-list)
body
end
Example:
function wakeup (message)
printf ("\a%s\n",
message);
end
wakeup ("Rise and shine!");
Return Single Value
function ret-var = name (arg-list)
body
end
Example:
function retval = avg (v)
retval = sum (v) / length (v);
end
Return Multiple Values
function [ret-var1,ret-var2,…,ret-varn] = name (arg-list)
body
end
Example:
function [mu,sigma] = basicStat(X)
mu = mean(X);
sigma = std(X);
end
Statements
IF Statement
if (condition)
then-body
elseif (condition)
elseif-body
else
else-body
end
Example:
if (rem (x, 2) == 0)
printf ("x is even\n");
elseif (rem (x, 3) == 0)
printf ("x is odd and divisible by 3\n");
else
printf ("x is odd\n");
end
Note that the elseif
keyword must not be spelled else if, as is allowed in Fortran. If
it is, the space between the else and if will tell
Octave to treat this as a new if statement within another if
statement's else clause
SWITCH Statement
switch (X)
case 1
do_something ();
case 2
do_something_else ();
otherwise
do_something_completely_different ();
end
Example:
A = 7;
switch A
case { 6, 7 }
printf ("variable is either 6 or 7\n");
otherwise
printf ("variable is neither 6 nor 7\n");
end
One advantage of using the switch statement compared to using
if statements is that the labels can be strings
switch (X)
case "a string"
do_something
...
endswitch
WHILE Statement
while (condition)
body
end
Example:
fib = ones (1, 10);
i = 3;
while (i <= 10)
fib (i) = fib (i-1) + fib (i-2);
i++;
end
DO-UNTIL Statement
do
body
until (condition)
Example:
fib = ones (1, 10);
i = 2;
do
i++;
fib (i) = fib (i-1) + fib (i-2);
until (i == 10)
FOR Statement
for var = expression
body
end
Example:
fib = ones (1, 10);
for i = 3:10
fib (i) = fib (i-1) + fib (i-2);
end
Iscriviti a:
Post (Atom)