|
Saturday, 28 January 2012 01:36 |
// function for getting comma seperated values in sql server
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
-- This function splits a variable-length parameter array (actually a string
-- with comma as a delimiter) and stored the values into the table.
-- CHARINDEX() function is used to identify the position of the first delimiter
-- in the text and SUBSTRING() function is used to set the 'element' column.
ALTER FUNCTION [dbo].[GetCSVValues](
@string varchar(550) -- '1,2,3,5,6,7'
)
RETURNS @table TABLE(element int)
AS
BEGIN
DECLARE @tempvarchar(550),
@delimPos AS tinyint
SET @delimPos = 0
SET @temp= LTRIM(RTRIM(@string))
WHILE CHARINDEX(',',@temp) > 0
BEGIN
SET @delimPos = CHARINDEX(',',@temp)
INSERT INTO @table(element) VALUES (CAST((LEFT(@temp,@delimPos-1)) AS smallint))
SET @temp= RTRIM(LTRIM(SUBSTRING(@temp,@delimPos+1,LEN(@temp)-@delimPos)))
END
INSERT INTO @table(element) VALUES (CAST((@temp) AS smallint))
RETURN
END
 Read more: |
|
Saturday, 22 October 2011 01:48 |
// create to_days(first_day), to_days(second_day) syntax that can be directly used while creating partitions based on month.
Create a table in the test database with the name "date_helper"
mysql> CREATE TABLE test.date_helper (
id INT NOT NULL,
to_timestamp datetime
);
Run the shell script and feed the output to mysql
unix> sh mydate.sh | mysql -uUser -pPassWd test
_____
The altered table will look something like this:
CREATE TABLE `date_helper` (
`id` int(11) NOT NULL,
`to_timestamp` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (to_days(to_timestamp))
(PARTITION January2010 VALUES IN (734138,734139,734140, ...) ENGINE = InnoDB,
PARTITION February2010 VALUES IN (734169,734170,734171,... ) ENGINE = InnoDB,
...
...
PARTITION November2011 VALUES IN (734807,734808,734809,...) ENGINE = InnoDB,
PARTITION December2011 VALUES IN (734837,734838,734839,...) ENGINE = InnoDB) */
_____
You can change the number of years as well as the starting month while running the script. If you want 3 years (365*3) partition starting from Jan 1990, use the following syntax:
sh mydate.sh 1990-01-01 1096
#!/bin/sh
# change the table name and partition column name below
tblname='date_helper'
colname='to_timestamp'
# if no start date is specified, then set the default date to 1 Jan 2010
startdate=${1:-'2010-01-01'}
# 24 partitions for 2 years viz 2010 and 2011 will be created by default
totaldate=${2:-'730'}
# change the days above from 730 for 2 years to 365 for 1 year
# run the script and save the output to a file > torun.sql
# open the file torun.sql and remove the last comma , before executing the query
# mysql test < torun.sql
# CREATE TABLE employees (id INT NOT NULL, store_date date)
# sample table
mysql -e"create table if not exists test.date_helper (id int, to_timestamp datetime);"
mysql -e"drop table if exists test.mycalendar;"
mysql -e"create table test.mycalendar (id int not null auto_increment, dateval date, primary key (id));"
echo "ALTER TABLE $tblname PARTITION BY LIST(to_days($colname)) ("
for (( i = 0 ; i < $totaldate ; i++ ))
do
mysql -e"insert into test.mycalendar (dateval) select '$startdate' + interval $i day;"
done
mysql -Bse"select concat(' PARTITION ', concat(monthname(dateval), extract(year from dateval)), ' VALUES IN ( ') as '', group_concat(concat('to_days(', '\'',dateval,'\')') order by dateval) as '', '), ' as '' from test.mycalendar group by extract(year_month from dateval);" | sed '$s/,//31'
echo ');'
exit
# use the following syntax to use
# sh /root/calendar.sh '2002-01-01'
## Use the output to create monthly partitions with date
CREATE TABLE employees (
id INT NOT NULL,
store_date date
)
PARTITION BY LIST(to_days(store_date)) (
PARTITION Jan02 VALUES IN (
to_days('2002-01-01'),to_days('2002-01-02'),...
),
PARTITION Feb02 VALUES IN (
to_days('2002-02-01'),to_days('2002-02-02'),...
)
);
# monthly partitions less than 40 years are recommended, in other words not more than 480 partitions
 Read more: |
|
|
Friday, 19 March 2010 12:45 |
Calculate point estimate by leaving out null values.
mean(height.survey, na.rm=TRUE) # na.rm=TRUE skips empty values.
 Read more: |
|
Written by
|
|
Monday, 15 February 2010 20:21 |
// Sql Examples
CREATE TABLE STATION
(ID INTEGER PRIMARY KEY,
CITY CHAR(20),
STATE CHAR(2),
LAT_N REAL,
LONG_W REAL);
populate the table STATION with a few rows:
INSERT INTO STATION VALUES (13, 'Phoenix', 'AZ', 33, 112);
INSERT INTO STATION VALUES (44, 'Denver', 'CO', 40, 105);
INSERT INTO STATION VALUES (66, 'Caribou', 'ME', 47, 68);
query to look at table STATION in undefined order:
SELECT * FROM STATION;
ID CITY STATE LAT_N LONG_W
13 Phoenix AZ 33 112
44 Denver CO 40 105
66 Caribou ME 47 68
query to select Northern stations (Northern latitude > 39.7):
-- selecting only certain rows is called a "restriction".
SELECT * FROM STATION
WHERE LAT_N > 39.7;
ID CITY STATE LAT_N LONG_W
44 Denver CO 40 105
66 Caribou ME 47 68
query to select only ID, CITY, and STATE columns:
-- selecting only certain columns is called a "projection".
SELECT ID, CITY, STATE FROM STATION;
ID CITY STATE
13 Phoenix AZ
44 Denver CO
66 Caribou ME
query to both "restrict" and "project":
SELECT ID, CITY, STATE FROM STATION
WHERE LAT_N > 39.7;
ID CITY STATE
44 Denver CO
66 Caribou ME
create another table to store normalized temperature and precipitation data:
-- ID field must match some STATION table ID
(so name and location will be known).
-- allowable ranges will be enforced for other values.
-- no duplicate ID and MONTH combinations.
-- temperature is in degrees Fahrenheit.
-- rainfall is in inches.
CREATE TABLE STATS
(ID INTEGER REFERENCES STATION(ID),
MONTH INTEGER CHECK (MONTH BETWEEN 1 AND 12),
TEMP_F REAL CHECK (TEMP_F BETWEEN -80 AND 150),
RAIN_I REAL CHECK (RAIN_I BETWEEN 0 AND 100),
PRIMARY KEY (ID, MONTH));
populate the table STATS with some statistics for January and July:
INSERT INTO STATS VALUES (13, 1, 57.4, 0.31);
INSERT INTO STATS VALUES (13, 7, 91.7, 5.15);
INSERT INTO STATS VALUES (44, 1, 27.3, 0.18);
INSERT INTO STATS VALUES (44, 7, 74.8, 2.11);
INSERT INTO STATS VALUES (66, 1, 6.7, 2.10);
INSERT INTO STATS VALUES (66, 7, 65.8, 4.52);
query to look at table STATS in undefined order:
SELECT * FROM STATS;
ID MONTH TEMP_F RAIN_I
13 1 57.4 .31
13 7 91.7 5.15
44 1 27.3 .18
44 7 74.8 2.11
66 1 6.7 2.1
66 7 65.8 4.52
query to look at table STATS, picking up location information by joining with table STATION on the ID column:
-- matching two tables on a common column is called a "join".
-- the column names often match, but this is not required.
-- only the column values are required to match.
SELECT * FROM STATION, STATS
WHERE STATION.ID = STATS.ID;
ID CITY ST LAT_N LONG_W ID MONTH TEMP_F RAIN_I
13 Phoenix AZ 33 112 13 1 57.4 .31
13 Phoenix AZ 33 112 13 7 91.7 5.15
44 Denver CO 40 105 44 1 27.3 .18
44 Denver CO 40 105 44 7 74.8 2.11
66 Caribou ME 47 68 66 1 6.7 2.1
66 Caribou ME 47 68 66 7 65.8 4.52
barbie oyunları Read more: |
|