Forum

Kognitio Console issues, advice, etc.
Contributor
Offline
Posts: 184
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

How can I do max,min for all columns in a table?

by MikeAtkinson » Thu Mar 13, 2014 9:40 am

I have some tables/views which are constantly being changed, they contain only numeric data, how do I find the max, min and other statistics of each column?
Reply with quote Top
Contributor
Offline
Posts: 184
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

Re: How can I do max,min for all columns in a table?

by MikeAtkinson » Thu Mar 13, 2014 9:42 am

Using KogScript it is easy to create a function to create a view of the tables containing the max and min of each column.

Code: Select all

function max_min(name, view)
  local t = select top 1 * from $name;
  local s = "create view ".. view .. " as select "
  for i=0,#t.colNames do
      s = s .. "max(" .. t.colNames[i] .. "), min(" .. t.colNames[i] .. ")";
  end
  s = s .. " from " .. name;
  return s;
end


sql( max_min("s1.t1", "s1.v1"));
Reply with quote Top
Contributor
Offline
Posts: 184
Joined: Wed May 29, 2013 2:10 pm
Location: Bracknell

Re: How can I do max,min for all columns in a table?

by MikeAtkinson » Thu Mar 13, 2014 9:44 am

Alternatively a table may be created showing the statistics from multiple tables with the same numbers of columns.

Code: Select all

function max_min(name, table)
  local t = select top 1 * from $name;
  local s = "insert into ".. table .. "  select '" .. name .."'"
  for i=0,#t.colNames do
      s = s .. ", max(" .. t.colNames[i] .. "), min(" .. t.colNames[i] .. ")";
  end
  s = s .. " from " .. name;
  return s;
end

function create_max_min_table(name, table)
  drop table $table;
  local t = select top 1 * from $name;
  local s = "create table ".. table .. " ( tname varchar(255)"
  for i=0,#t.colNames do
      s = s .. ", " .. t.colNames[i] .. "_max int, " .. t.colNames[i] .. "_min int";
  end
  s = s .. " ) ";
  sql( s );
end


-- create a couple of tables
drop table s1.t5;
create table s1.t5 (a int, b int, c int);
for i=1,20 do
    insert into s1.t5 values($i, $( i*7 % 53), $(3-i) );
end

drop table s1.t6;
create table s1.t6 (a int, b int, c int);
for i=1,20 do
    insert into s1.t6 values($i, $( i*11 % 101), $(15-i) );
end


create_max_min_table("s1.t5", "s1.max_min")
sql( max_min("s1.t5", "s1.max_min"));
sql( max_min("s1.t6", "s1.max_min"));
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron