SitePoint Sponsor

User Tag List

Results 1 to 24 of 24

Thread: Moving average

Hybrid View

  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Moving average

    Moving average

    Hello guys, I've problem with MySQL and need your help.

    I attach the file xlsx in the test.zip.

    This file xlsx is able to calculate the moving average (column G - AVERAGE_12_MONTHS) of the column F (F_SUM).

    The average is dynamic because in the column G is a line feed, e.g.:

    In the cell G13 I've the average of cell F2:F13, in the cell G14 the average is of cells F3:F14, in the cell G15 the average is of cells F4:F15... etc

    I need realize the same average in mysql, any suggestion?
    Can you help me?
    Any help would be very appreciated
    Attached Files Attached Files

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    1. i cannot read zip files
    2. i don't want to read xlsx files

    please restate your question in terms of database tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    1. i cannot read zip files
    2. i don't want to read xlsx files

    please restate your question in terms of database tables
    Ok, I understand.

    In this table `page1-1` I need:
    1. In column `F_SUM` update with sum of colums values `F1`, `F2` and `F3`
    2. In column calculate `AVERAGE_12_MONTHS` the dynamic average of the column `F_SUM`

    The average is dynamic because in the column `AVERAGE_12_MONTHS` is a line feed, e.g.:
    • In the record number 12 I've the average of records 1,2,3,4,5,6,7,8,9,10,11 and 12
    • In the record number 13 I've the average of records 2,3,4,5,6,7,8,9,10,11,12 and 13
    • In the record number 14 I've the average of records 3,4,5,6,7,8,9,10,11,12,13 and 14
    • .....

    Code:
    -- ----------------------------
    -- Table structure for `page1-1`
    -- ----------------------------
    DROP TABLE IF EXISTS `page1-1`;
    CREATE TABLE `page1-1` (
      `YEARS` varchar(255) DEFAULT NULL,
      `MONTHS` varchar(255) DEFAULT NULL,
      `F1` varchar(255) DEFAULT NULL,
      `F2` varchar(255) DEFAULT NULL,
      `F3` varchar(255) DEFAULT NULL,
      `F_SUM` varchar(255) DEFAULT NULL,
      `AVERAGE_12_MONTHS` varchar(255) DEFAULT NULL,
      `VAR` varchar(255) DEFAULT NULL,
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of page1-1
    -- ----------------------------
    INSERT INTO `page1-1` VALUES ('2009', '05', '92.626,4', '32.796,56', '59.924,640002', '', '', '', '1');
    INSERT INTO `page1-1` VALUES ('2009', '06', '95.008,64', '31.933,6', '56.977,839993', '', '', '', '2');
    INSERT INTO `page1-1` VALUES ('2009', '07', '111.853,12', '33.091,92', '53.262,400004', '', '', '', '3');
    INSERT INTO `page1-1` VALUES ('2009', '08', '108.379,92', '32.500,48', '60.452,640005', '', '', '', '4');
    INSERT INTO `page1-1` VALUES ('2009', '09', '102.605,44', '34.639,36', '58.108,399992', '', '', '', '5');
    INSERT INTO `page1-1` VALUES ('2009', '10', '83.442,56', '31.952,400002', '52.181,6', '', '', '', '6');
    INSERT INTO `page1-1` VALUES ('2009', '11', '55.527,84', '24.864', '47.962,800004', '', '', '', '7');
    INSERT INTO `page1-1` VALUES ('2009', '12', '45.067,28', '21.627,28', '50.793,6', '', '', '', '8');
    INSERT INTO `page1-1` VALUES ('2010', '01', '50.108,6386', '32.070,35916', '88.992,39535', '', '', '', '9');
    INSERT INTO `page1-1` VALUES ('2010', '02', '45.229,00261', '32.119,99696', '57.848,00256', '', '', '', '10');
    INSERT INTO `page1-1` VALUES ('2010', '03', '51.329,99474', '34.104,99888', '64.472,82168', '', '', '', '11');
    INSERT INTO `page1-1` VALUES ('2010', '04', '47.097,99864', '38.268,00225', '77.124,0064', '', '', '', '12');
    INSERT INTO `page1-1` VALUES ('2010', '05', '43.667,00318', '31.968,99992', '108.472,0064', '', '', '', '13');
    INSERT INTO `page1-1` VALUES ('2010', '06', '55.458,99688', '32.521,00124', '103.608', '', '', '', '14');
    INSERT INTO `page1-1` VALUES ('2010', '07', '99.716,0032', '33.510,99578', '74.126,99424', '', '', '', '15');
    INSERT INTO `page1-1` VALUES ('2010', '08', '101.051,99552', '31.377,0024', '67.117,99712', '', '', '', '16');
    INSERT INTO `page1-1` VALUES ('2010', '09', '72.834,00258', '35.291,00016', '81.023,00416', '', '', '', '17');
    INSERT INTO `page1-1` VALUES ('2010', '10', '42.791,00364', '44.317,0016', '87.538,9998', '', '', '', '18');
    INSERT INTO `page1-1` VALUES ('2010', '11', '41.505,99693', '32.237,00012', '66.770,9952', '', '', '', '19');
    INSERT INTO `page1-1` VALUES ('2010', '12', '38.599,005', '27.211,99872', '61.212,99744', '', '', '', '20');
    INSERT INTO `page1-1` VALUES ('2011', '01', '55.458,99761', '28.852,99888', '60.279,0048', '', '', '', '21');
    INSERT INTO `page1-1` VALUES ('2011', '02', '46.786,00489', '29.952,00224', '58.459,00032', '', '', '', '22');
    INSERT INTO `page1-1` VALUES ('2011', '03', '52.550,00568', '32.447,00064', '64.910,76056', '', '', '', '23');
    INSERT INTO `page1-1` VALUES ('2011', '04', '39.288,9992', '41.818,0032', '81.383,00113', '', '', '', '24');
    INSERT INTO `page1-1` VALUES ('2011', '05', '84.902,99957', '32.890,002', '66.315,99776', '', '', '', '25');
    INSERT INTO `page1-1` VALUES ('2011', '06', '94.883,73246', '35.711,45084', '60.993,83012', '', '', '', '26');
    INSERT INTO `page1-1` VALUES ('2011', '07', '113.265,999', '38.767,0016', '55.319,98993', '', '', '', '27');
    INSERT INTO `page1-1` VALUES ('2011', '08', '122.653,8295', '28.179,25128', '48.713,92516', '', '', '', '28');
    INSERT INTO `page1-1` VALUES ('2011', '09', '118.939,00216', '25.380,0064', '44.827,99808', '', '', '', '29');
    INSERT INTO `page1-1` VALUES ('2011', '10', '72.671,00321', '35.154,0034', '66.821,9968', '', '', '', '30');
    INSERT INTO `page1-1` VALUES ('2011', '11', '52.688,03987', '29.032,06384', '58.792,89148', '', '', '', '31');
    INSERT INTO `page1-1` VALUES ('2011', '12', '50.778,1824', '24.184,41893', '52.061,39352', '', '', '', '32');
    INSERT INTO `page1-1` VALUES ('2012', '01', '67.452', '27.755,99892', '49.381,99808', '', '', '', '33');
    INSERT INTO `page1-1` VALUES ('2012', '02', '65.157,00029', '25.064,9984', '44.974,99776', '', '', '', '34');
    INSERT INTO `page1-1` VALUES ('2012', '03', '51.895,99976', '35.507,99695', '62.303,66252', '', '', '', '35');
    INSERT INTO `page1-1` VALUES ('2012', '04', '59.680,0026', '34.401,99684', '68.407,00416', '', '', '', '36');
    INSERT INTO `page1-1` VALUES ('2012', '05', '76.293,00236', '36.553,00128', '71.262,99872', '', '', '', '37');
    INSERT INTO `page1-1` VALUES ('2012', '06', '91.630,00308', '36.862,00284', '63.096,99387', '', '', '', '38');
    INSERT INTO `page1-1` VALUES ('2012', '07', '121.355,00758', '35.470,99968', '50.525,99904', '', '', '', '39');
    INSERT INTO `page1-1` VALUES ('2012', '08', '104.053,9946', '38.849,00088', '56.643,00224', '', '', '', '40');
    INSERT INTO `page1-1` VALUES ('2012', '09', '87.243,99989', '38.957,9976', '62.945,9968', '', '', '', '41');
    INSERT INTO `page1-1` VALUES ('2012', '10', '75.514,00438', '35.562,0016', '63.570,9984', '', '', '', '42');
    INSERT INTO `page1-1` VALUES ('2012', '11', '47.608,00044', '30.661,00425', '62.245,0048', '', '', '', '43');
    INSERT INTO `page1-1` VALUES ('2012', '12', '13.527,2', '10.924,04032', '26.763,04036', '', '', '', '44');
    INSERT INTO `page1-1` VALUES ('2013', '01', '15.955,44', '9.888,8', '18.707,36', '', '', '', '45');
    INSERT INTO `page1-1` VALUES ('2013', '02', '14.813,76', '9.452,24', '17.036,08', '', '', '', '46');
    INSERT INTO `page1-1` VALUES ('2013', '03', '13.567,92', '9.950,64', '18.263,44', '', '', '', '47');
    INSERT INTO `page1-1` VALUES ('2013', '04', '12.965,6', '8.086', '17.543,6', '', '', '', '48');
    INSERT INTO `page1-1` VALUES ('2013', '05', '12.363,76', '8.034,64', '16.605,04', '', '', '', '49');
    INSERT INTO `page1-1` VALUES ('2013', '06', '15.822,88', '8.388,88', '16.211,92', '', '', '', '50');
    INSERT INTO `page1-1` VALUES ('2013', '07', '21.269,04', '9.082,4', '16.352,48', '', '', '', '51');
    INSERT INTO `page1-1` VALUES ('2013', '08', '18.727,44', '10.274,16', '18.108,96', '', '', '', '52');
    INSERT INTO `page1-1` VALUES ('2013', '09', '16.470,56', '8.159,76', '16.271,92', '', '', '', '53');

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    In column `F_SUM` update with sum of colums values `F1`, `F2` and `F3`
    let's start with this problem
    Code:
    UPDATE `page1-1` 
       SET F_sum = F1 + F2 + F3
    this gives the error message "Truncated incorrect DOUBLE value 59.924,640002"

    i think the problem is your use of VARCHAR(255) for numeric values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    let's start with this problem
    Code:
    UPDATE `page1-1` 
       SET F_sum = F1 + F2 + F3
    this gives the error message "Truncated incorrect DOUBLE value 59.924,640002"

    i think the problem is your use of VARCHAR(255) for numeric values
    thank you for help.
    this is new version of table `page1-1`.
    I've update in column `F_SUM` with sum of colums values `F1`, `F2` and `F3`.

    But I can not calculate the average dynamic... because in the column `AVERAGE_12_MONTHS` is a line feed, e.g.:
    • In the record number 12 I've the average of records 1,2,3,4,5,6,7,8,9,10,11 and 12
    • In the record number 13 I've the average of records 2,3,4,5,6,7,8,9,10,11,12 and 13
    • In the record number 14 I've the average of records 3,4,5,6,7,8,9,10,11,12,13 and 14
    • .....

    Code:
    -- ----------------------------
    -- Table structure for `page1-1`
    -- ----------------------------
    DROP TABLE IF EXISTS `page1-1`;
    CREATE TABLE `page1-1` (
      `YEARS` int(11) DEFAULT NULL,
      `MONTHS` int(11) DEFAULT NULL,
      `F1` decimal(10,4) DEFAULT NULL,
      `F2` decimal(10,4) DEFAULT NULL,
      `F3` decimal(10,4) DEFAULT NULL,
      `F_SUM` varchar(255) DEFAULT NULL,
      `AVERAGE_12_MONTHS` varchar(255) DEFAULT NULL,
      `VAR` varchar(255) DEFAULT NULL,
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=54 DEFAULT CHARSET=latin1;
    
    -- ----------------------------
    -- Records of page1-1
    -- ----------------------------
    INSERT INTO `page1-1` VALUES ('2009', '5', '92626.4000', '32796.5600', '59924.6400', '185347.6000', '', '', '1');
    INSERT INTO `page1-1` VALUES ('2009', '6', '95008.6400', '31933.6000', '56977.8400', '183920.0800', '', '', '2');
    INSERT INTO `page1-1` VALUES ('2009', '7', '111853.1200', '33091.9200', '53262.4000', '198207.4400', '', '', '3');
    INSERT INTO `page1-1` VALUES ('2009', '8', '108379.9200', '32500.4800', '60452.6400', '201333.0400', '', '', '4');
    INSERT INTO `page1-1` VALUES ('2009', '9', '102605.4400', '34639.3600', '58108.4000', '195353.2000', '', '', '5');
    INSERT INTO `page1-1` VALUES ('2009', '10', '83442.5600', '31952.4000', '52181.6000', '167576.5600', '', '', '6');
    INSERT INTO `page1-1` VALUES ('2009', '11', '55527.8400', '24864.0000', '47962.8000', '128354.6400', '', '', '7');
    INSERT INTO `page1-1` VALUES ('2009', '12', '45067.2800', '21627.2800', '50793.6000', '117488.1600', '', '', '8');
    INSERT INTO `page1-1` VALUES ('2010', '1', '50108.6386', '32070.3592', '88992.3954', '171171.3932', '', '', '9');
    INSERT INTO `page1-1` VALUES ('2010', '2', '45229.0026', '32119.9970', '57848.0026', '135197.0022', '', '', '10');
    INSERT INTO `page1-1` VALUES ('2010', '3', '51329.9947', '34104.9989', '64472.8217', '149907.8153', '', '', '11');
    INSERT INTO `page1-1` VALUES ('2010', '4', '47097.9986', '38268.0023', '77124.0064', '162490.0073', '', '', '12');
    INSERT INTO `page1-1` VALUES ('2010', '5', '43667.0032', '31968.9999', '108472.0064', '184108.0095', '', '', '13');
    INSERT INTO `page1-1` VALUES ('2010', '6', '55458.9969', '32521.0012', '103608.0000', '191587.9981', '', '', '14');
    INSERT INTO `page1-1` VALUES ('2010', '7', '99716.0032', '33510.9958', '74126.9942', '207353.9932', '', '', '15');
    INSERT INTO `page1-1` VALUES ('2010', '8', '101051.9955', '31377.0024', '67117.9971', '199546.9950', '', '', '16');
    INSERT INTO `page1-1` VALUES ('2010', '9', '72834.0026', '35291.0002', '81023.0042', '189148.0070', '', '', '17');
    INSERT INTO `page1-1` VALUES ('2010', '10', '42791.0036', '44317.0016', '87538.9998', '174647.0050', '', '', '18');
    INSERT INTO `page1-1` VALUES ('2010', '11', '41505.9969', '32237.0001', '66770.9952', '140513.9922', '', '', '19');
    INSERT INTO `page1-1` VALUES ('2010', '12', '38599.0050', '27211.9987', '61212.9974', '127024.0011', '', '', '20');
    INSERT INTO `page1-1` VALUES ('2011', '1', '55458.9976', '28852.9989', '60279.0048', '144591.0013', '', '', '21');
    INSERT INTO `page1-1` VALUES ('2011', '2', '46786.0049', '29952.0022', '58459.0003', '135197.0074', '', '', '22');
    INSERT INTO `page1-1` VALUES ('2011', '3', '52550.0057', '32447.0006', '64910.7606', '149907.7669', '', '', '23');
    INSERT INTO `page1-1` VALUES ('2011', '4', '39288.9992', '41818.0032', '81383.0011', '162490.0035', '', '', '24');
    INSERT INTO `page1-1` VALUES ('2011', '5', '84902.9996', '32890.0020', '66315.9978', '184108.9994', '', '', '25');
    INSERT INTO `page1-1` VALUES ('2011', '6', '94883.7325', '35711.4508', '60993.8301', '191589.0134', '', '', '26');
    INSERT INTO `page1-1` VALUES ('2011', '7', '113265.9990', '38767.0016', '55319.9899', '207352.9905', '', '', '27');
    INSERT INTO `page1-1` VALUES ('2011', '8', '122653.8295', '28179.2513', '48713.9252', '199547.0060', '', '', '28');
    INSERT INTO `page1-1` VALUES ('2011', '9', '118939.0022', '25380.0064', '44827.9981', '189147.0067', '', '', '29');
    INSERT INTO `page1-1` VALUES ('2011', '10', '72671.0032', '35154.0034', '66821.9968', '174647.0034', '', '', '30');
    INSERT INTO `page1-1` VALUES ('2011', '11', '52688.0399', '29032.0638', '58792.8915', '140512.9952', '', '', '31');
    INSERT INTO `page1-1` VALUES ('2011', '12', '50778.1824', '24184.4189', '52061.3935', '127023.9948', '', '', '32');
    INSERT INTO `page1-1` VALUES ('2012', '1', '67452.0000', '27755.9989', '49381.9981', '144589.9970', '', '', '33');
    INSERT INTO `page1-1` VALUES ('2012', '2', '65157.0003', '25064.9984', '44974.9978', '135196.9965', '', '', '34');
    INSERT INTO `page1-1` VALUES ('2012', '3', '51895.9998', '35507.9970', '62303.6625', '149707.6593', '', '', '35');
    INSERT INTO `page1-1` VALUES ('2012', '4', '59680.0026', '34401.9968', '68407.0042', '162489.0036', '', '', '36');
    INSERT INTO `page1-1` VALUES ('2012', '5', '76293.0024', '36553.0013', '71262.9987', '184109.0024', '', '', '37');
    INSERT INTO `page1-1` VALUES ('2012', '6', '91630.0031', '36862.0028', '63096.9939', '191588.9998', '', '', '38');
    INSERT INTO `page1-1` VALUES ('2012', '7', '121355.0076', '35470.9997', '50525.9990', '207352.0063', '', '', '39');
    INSERT INTO `page1-1` VALUES ('2012', '8', '104053.9946', '38849.0009', '56643.0022', '199545.9977', '', '', '40');
    INSERT INTO `page1-1` VALUES ('2012', '9', '87243.9999', '38957.9976', '62945.9968', '189147.9943', '', '', '41');
    INSERT INTO `page1-1` VALUES ('2012', '10', '75514.0044', '35562.0016', '63570.9984', '174647.0044', '', '', '42');
    INSERT INTO `page1-1` VALUES ('2012', '11', '47608.0004', '30661.0043', '62245.0048', '140514.0095', '', '', '43');
    INSERT INTO `page1-1` VALUES ('2012', '12', '13527.2000', '10924.0403', '26763.0404', '51214.2807', '', '', '44');
    INSERT INTO `page1-1` VALUES ('2013', '1', '15955.4400', '9888.8000', '18707.3600', '44551.6000', '', '', '45');
    INSERT INTO `page1-1` VALUES ('2013', '2', '14813.7600', '9452.2400', '17036.0800', '41302.0800', '', '', '46');
    INSERT INTO `page1-1` VALUES ('2013', '3', '13567.9200', '9950.6400', '18263.4400', '41782.0000', '', '', '47');
    INSERT INTO `page1-1` VALUES ('2013', '4', '12965.6000', '8086.0000', '17543.6000', '38595.2000', '', '', '48');
    INSERT INTO `page1-1` VALUES ('2013', '5', '12363.7600', '8034.6400', '16605.0400', '37003.4400', '', '', '49');
    INSERT INTO `page1-1` VALUES ('2013', '6', '15822.8800', '8388.8800', '16211.9200', '40423.6800', '', '', '50');
    INSERT INTO `page1-1` VALUES ('2013', '7', '21269.0400', '9082.4000', '16352.4800', '46703.9200', '', '', '51');
    INSERT INTO `page1-1` VALUES ('2013', '8', '18727.4400', '10274.1600', '18108.9600', '47110.5600', '', '', '52');
    INSERT INTO `page1-1` VALUES ('2013', '9', '16470.5600', '8159.7600', '16271.9200', '40902.2400', '', '', '53');

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Miguel61 View Post
    this is new version of table `page1-1`.
    could you please also change VARCHAR(255) in F_SUM and AVERAGE_12_MONTHS as well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •