Showing posts with label MySQL. Show all posts
Showing posts with label MySQL. Show all posts

Conservative Forecast Based On Historic Data

You can always use mean on the historic data as your forecast, which is reasonable. However, this is radical, especially when not consider the probability distribution. To be conservative, you may need to add a standard deviation (σ):

Mean + σ
Or
Mean - σ

In general, 1 standard deviation is good enough. In normally distributed data, 1σ means the side coverage of 34.1%, accounts for 84.1% of total population.

If there is no noise data within the dataset, arithmetic mean (average) can be used. However, if there is noise data, interquartile mean (IQM) is recommended. Accordingly, the standard deviation should be also based on the interquartile range (middle 50%).

In MySQL, the built-in function is STD(expr). In Oracle, it is STDDEV(expr) and in T-SQL, it is STDEVP(expr).

http://en.wikipedia.org/wiki/Standard_deviation
http://koncordpartners.blogspot.com/2010/11/practical-mean.html

http://oreilly.com/catalog/sqlnut/chapter/ch04.html
http://www.devguru.com/technologies/t-sql/7097.asp

Schedule A Database Job In MySQL

As of MySQL 5.1, one can use event to schedule a job:

CREATE EVENT event_name
ON SCHEDULE schedule
DO event_body
;

schedule can be: AT timestamp [+ INTERVAL interval] ...
or EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]

interval can be: quantity followed by unit
unit can be: {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

event_body can be SQL query or EXECUTE sp_name.

For instance:
CREATE EVENT event_name
ON SCHEDULE EVERY 5 MINUTE
DO SELECT * FROM tab
;

ALTER, DROP and SET GLOBAL event_name = 1 to switch on the event, etc.


http://dev.mysql.com/doc/refman/5.1/en/create-event.html
http://answers.oreilly.com/topic/177-how-to-use-events-to-schedule-database-actions-in-mysql/
http://rpbouman.blogspot.com/2005/10/scheduling-procedure-execution-in.html

Delete or Select Records Which Is In Or Not In A View

MySQL

Delete records in a table based on the condition that keys of that table is in a another view or is not in the view will not work in MySQL, because either IN or NOT IN needs to be followed by a string delimited by comma. Sure, you can program it to turn a view into a string by insert comma in it. Following is an example which does not work in MySQL:

DELETE FROM tab
WHERE tab.id IN
( SELECT tab.id
FROM tab
WHERE ...
)

An easier way is to use INNER JOIN method to accomplish the task. Here is how to do this:

DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE ...
) AS vCondition
ON vCondition.joinKey = tab.joinKey

Please remember between DELETE and FROM you would need to insert what is going to be deleted. In this case, it is tab.*, represents all fields in the records which meets the condition.

For NOT IN condition, you would need to make sure what is going to be joined would be opposite to NOT IN condition:

DELETE tab.* FROM tab
INNER JOIN
( SELECT tab.id
FROM tab
WHERE tab.id NOT IN
( SELECT tab.id
FROM tab
WHERE ...
)) AS vCondition
ON vCondition.joinKey = tab.joinKey

Don't ask why this time NOT IN works. If you do, please ask MySQL developers directly why they make life so hard. At the end, it is free. So, would you please shut up?

T-SQL

Delete or select records in a table based on the condition that keys of that table is in a another view or is not in the view will work in SQL Server. However, if that is dynamic T-SQL, it won't work. Following is the solution:

SELECT @Columns = COALESCE( @Columns + ',[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR) + ']'
, '[' + CAST(LTRIM(RTRIM(Pivot_Column)) AS VARCHAR)+ ']'
)
FROM
( SELECT DISTINCT Pivot_Column
FROM Temp_Transform_Input
) AS Base_Q
SET @Query = ' SELECT *
INTO Temp_Transform_Result
FROM
( SELECT *
FROM Temp_Transform_Input
) AS Inner_Q
PIVOT
( SUM(Content_Column)
FOR Pivot_Column IN (' + @Columns + ')
) AS Pivot_Q
'
EXECUTE(@Query)


http://www.electrictoolbox.com/article/mysql/cross-table-delete/

What Does Size Mean in Numberic Datatype of MySQL

What does 3 in TINYINT(3) or 11 in INT(11) mean?

It does mean neither bit or byte size of the datatype. The datatype itself has already specified the size of the storage. It is for display purpose. Following paragraphs come from MySQL Manual, http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html:

"Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range allowed by three characters are displayed using more than three characters.

When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, because in these cases MySQL assumes that the data fits into the original column width. "

To check the size for each of these datatype, please go to same URL: http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html.

Reference:

http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html
http://alexander.kirk.at/2007/08/24/what-does-size-in-intsize-of-mysql-mean/

Unicode Like Chinese Japanese Korean Characters In and Out MySQL Through PHP & Java

Both MySQL and PHP/Java support the Unicode, namely UTF-8. To be able to store into and retrieve these Unicode characters in MySQL database, one would need to do following things:

MySQL needs to be informed through PHP/Java. So, following code needs to be placed just after the connection. If the connection is centralized, this code is better to be in that centralized file:

PHP: mysql_query("SET NAMES 'UTF8'");
Java: stmt.execute("SET NAMES 'utf8'");

For all your PHP files generates HTML scripts, please include following code at top of the file:

header("Content-Type: text/html; charset=UTF-8");

For all your HTML files, following script is needed in the head of HTML:

‹ meta http-equiv="Content-Type" content="text/html; charset=UTF-8" ›

Done. Please note, in first piece of PHP code, it is UTF8 and in second PHP code and last HTML script it is UTF-8. Else, it won’t work properly.

If your codes include any of following piece, simply remove it:

PHP: mysql_query("SET CHARACTER SET 'UTF8'");
Java: stmt.execute("SET CHARACTER SET 'utf8'");

Labels