1 / 33

Characteristic Functions

Characteristic Functions. Characteristic Functions. (from fin_data table in Sybase Sample Database) Have: Year quarter code amount 2001 Q1 e1 198 2001 Q2 e1 204 2001 Q3 e1 214 2001 Q4 e1 231. Want: Year Code Q1Amt Q2Amt Q3Amt Q4Amt

emmett
Télécharger la présentation

Characteristic Functions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Characteristic Functions

  2. Characteristic Functions (from fin_data table in Sybase Sample Database) Have: Yearquartercodeamount 2001 Q1 e1 198 2001 Q2 e1 204 2001 Q3 e1 214 2001 Q4 e1 231 Want: YearCodeQ1AmtQ2AmtQ3AmtQ4Amt 2001 e1 198 204 214 231

  3. Characteristic Functions Have: Normalized Yearquartercodeamount 2001 Q1 e1 198 2001 Q2 e1 204 2001 Q3 e1 214 2001 Q4 e1 231 Want: Un-normalized YearCodeQ1AmtQ2AmtQ3AmtQ4Amt 2001 e1 198 204 214 231

  4. Characteristic Functions Similar to Excel Database Format Yearquartercodeamount 2001 Q1 e1 198 2001 Q2 e1 204 2001 Q3 e1 214 2001 Q4 e1 231 Pivot Table YearCodeQ1AmtQ2AmtQ3AmtQ4Amt 2001 e1 198 204 214 231

  5. Possible Solutions(without using Characteristic Functions) Self Join as in type 4 queries

  6. Possible Solution – Self Join Use a self join as in type 4 queries Start with just Q1 and Q2, code E1, Year 2001 YearcodeQ1AmtQ2Amt 2001 e1 198 204 Write the SQL query

  7. Possible Solution – Self Join YearcodeQ1AmtQ2Amt 2001 e1 198 204 SELECT F1.year, F1.code, F1.amount AS Q1Amt, F2.amount AS Q2Amt FROM fin_data F1, fin_data F2 WHERE F1.year = 2001 // Only 2001 AND F2.year = 2001 AND F1.code = ‘e1’ // Only financial code e1 AND F2.code = ‘e1’ AND F1.quarter = ‘Q1’ // Get Q1 amount AND F2.quarter = ‘Q2’ // Get Q2 amount Code

  8. Possible Solution – Self Join Expand to all years YearcodeQ1AmtQ2Amt 1999 e1 101 93 2000 e1 153 149 2001 e1 198 204

  9. Possible Solution – Self Join Expand to all years YearcodeQ1AmtQ2Amt 1999 e1 101 93 2000 e1 153 149 2001 e1 198 204 Code SELECT F1.year, F1.code, F1.amount AS Q1Amt, F2.amount AS Q2Amt FROM fin_data F1, fin_data F2 WHERE F1.year = F2.year// Same Year AND F1.code = ‘e1’ // Only financial code e1 AND F2.code = ‘e1’ AND F1.quarter = ‘Q1’ // Get Q1 amount AND F2.quarter = ‘Q2’ // Get Q2 amount

  10. Possible Solution – Self Join Expand to all four quarters YearcodeQ1AmtQ2AmtQ3AmtQ4Amt 1999 e1 101 93 129 145 2000 e1 153 149 157 163 2001 e1 198 204 214 231

  11. Possible Solution – Self Join All four quarters YearcodeQ1AmtQ2AmtQ3AmtQ4Amt 1999 e1 101 93 129 145 2000 e1 153 149 157 163 2001 e1 198 204 214 231 Code SELECT F1.year, F1.code, F1.amount AS Q1Amt, F2.amount AS Q2Amt, F3.amount AS Q3Amt, F4.amount AS Q4Amt FROM fin_data F1, fin_data F2, fin_data F3, fin_data F4 WHERE F1.year = F2.year // Same Year AND F2.year = F3.year AND F3.year = F4.year AND F1.code = ‘e1’ // Only financial code e1 AND F2.code = ‘e1’ AND F3.code = ‘e1’ AND F4.code = ‘e1’ AND F1.quarter = ‘Q1’ // One record for each quarter AND F2.quarter = ‘Q2’ AND F3.quarter = ‘Q3’ AND F4.quarter = ‘Q4’

  12. Possible Solution – Self Join Problems Coding: Suppose we wanted months instead of quarters… Performance: Suppose fin_data had 100,000 records instead of 84… We need a better solution!

  13. Possible Solutions(without using Characteristic Functions) SubQueries

  14. Possible Solution – SubQueries Use a subquery as a field in the select clause Start with just Q1 and Q2, code E1, Year 2001 YearcodeQ1AmtQ2Amt 2001 e1 198 204 Write the SQL query

  15. Possible Solution – SubQueries Use a subquery as a field in the select clause Code SELECT F1.year, F1.code, F1.amount AS Q1Amt, ( SELECT F2.amount as Q2Amt FROM fin_data F2 WHERE F2.quarter = ‘Q2’ AND F2.code = ‘e1’ AND F2.year = 2001 ) FROM fin_data F1 WHERE F1.quarter = ‘Q1’ AND F1.code = ‘e1’ AND F1.year = 2001 YearcodeQ1AmtQ2Amt 2001 e1 198 204

  16. Possible Solution – SubQueries Expand to all years YearcodeQ1AmtQ2Amt 1999 e1 101 93 2000 e1 153 149 2001 e1 198 204

  17. Possible Solution – SubQueries Expand to all years Code SELECT F1.year, F1.code, F1.amount AS Q1Amt, ( SELECT F2.amount as Q2Amt FROM fin_data F2 WHERE F2.quarter = ‘Q2’ AND F2.code = ‘e1’ AND F2.year = F1.year ) FROM fin_data F1 WHERE F1.quarter = ‘Q1’ AND F1.code = ‘e1’ //AND F1.year = 2001 YearcodeQ1AmtQ2Amt 1999 e1 101 93 2000 e1 153 149 2001 e1 198 204 This is now a correlated subquery

  18. Possible Solution – SubQueries Expand to all four quarters YearcodeQ1AmtQ2AmtQ3AmtQ4Amt 1999 e1 101 93 129 145 2000 e1 153 149 157 163 2001 e1 198 204 214 231

  19. Possible Solution – SubQueries All four quarters YearcodeQ1AmtQ2AmtQ3AmtQ4Amt 1999 e1 101 93 129 145 2000 e1 153 149 157 163 2001 e1 198 204 214 231 Code SELECT F1.year, F1.code, F1.amount AS Q1Amt, ( SELECT F2.amount as Q2Amt FROM fin_data F2 WHERE F2.quarter = ‘Q2’ AND F2.code = ‘e1’ AND F2.year = F1.year ), ( SELECT F2.amount as Q3Amt FROM fin_data F2 WHERE F2.quarter = ‘Q3’ AND F2.code = ‘e1’ AND F2.year = F1.year ), ( SELECT F2.amount as Q4Amt FROM fin_data F2 WHERE F2.quarter = ‘Q4’ AND F2.code = ‘e1’ AND F2.year = F1.year ) FROM fin_data F1 WHERE F1.quarter = ‘Q1’ AND F1.code = ‘e1’

  20. Possible Solution – Self Join Coding: Again, Suppose we wanted months instead of quarters… Problems Performance: Our example requires the effective execution of 10 queries • The F1 query is run once to return the year, code and Q1Amt columns • The F2 query is run 9 times return the Q2, Q3, and Q4 amounts for 1999, 2000 and 2001 If our table had 100,000 rows F2 would run 300,000 times! We still need a better solution!

  21. Possible Solution – Temporary Table Strategy Create a table with fields for year, code, Q1Amt, Q2Amt, Q3Amt, and Q4Amt Insert Query to add records and fill in year, code, Q1Amt Update Query to add Q2Amt Update Query to add Q3Amt Update Query to add Q4Amt

  22. Possible Solution – Temporary Table Create a Table Create a table with fields for year, code, Q1Amt, Q2Amt, Q3Amt, and Q4Amt CREATE TABLE QAmt ( year char(4), code char(2), Q1Amt numeric(9), Q2Amt numeric(9), Q3Amt numeric(9), Q4Amt numeric(9) );

  23. Possible Solution – Temporary Table DROP a Table There will be times when you want to get rid of a table you have created. Perhaps you made an error when you created it. Or, you may simply be through using it. To get rid of a table you DROP it from the database. DROP TABLE QAmt

  24. Possible Solution – Temporary Table INSERT Query Insert Query to add records and fill in year, code, Q1Amt INSERT INTO QAmt (year, code, Q1Amt) SELECT year, code, amount FROM fin_data WHERE quarter = 'Q1' AND code = 'e1'

  25. Possible Solution – Temporary Table Delete Query There may also be times when you want to keep a table, but get rid of all the records in the table… to empty it. If, for example, you find a problem with your INSERT query, you may want to empty the table before you run the corrected INSERT query. To empty a table you DELETE all the records FROM the table. To empty the QAmt table you: DELETE FROM QAmt

  26. Possible Solution – Temporary Table After INSERT Query Check the results: SELECT * FROM QAmt YearcodeQ1AmtQ2AmtQ3AmtQ4Amt 1999 e1 101 (NULL) (NULL) (NULL) 2000 e1 153 (NULL) (NULL) (NULL) 2001 e1 198 (NULL) (NULL) (NULL)

  27. Possible Solution – Temporary Table Update Query Update Query to add Q2Amt UPDATE QAmt Q SET Q2Amt = ( SELECT amount FROM fin_data F WHERE F.year = Q.year AND F.code = Q.code AND F.quarter = 'Q2‘ ) We do two more update queries to fill in Q3Amt and Q4Amt YearcodeQ1AmtQ2AmtQ3AmtQ4Amt 1999 e1 101 93 (NULL) (NULL) 2000 e1 153 149 (NULL) (NULL) 2001 e1 198 204 (NULL) (NULL)

  28. Possible Solution – Temporary Table Is this really different At this point you may have noticed that the temporary table approach looks a lot like the subquery approach. In fact, it is virtually the same. It seems conceptually simpler because the temporary table allows us break down the problem into separate and distinct subtasks. We can complete part of the solution, check the results, complete some more, etc. For this reason, you will see it used. It has some legitimate applications, but this isn’t one of them. This point becomes even more evident if we use either the self-join approach or the subquery approach to populate the table in one step.

  29. Possible Solution – Temporary Table One Step Using Self Join INSERT INTO QAmt (year, code, Q1Amt, Q2Amt, Q3Amt, Q4Amt) SELECT F1.year, F1.code, F1.amount, F2.amount, F3.amount, F4.amount FROM fin_data F1, fin_data F2, fin_data F3, fin_data F4 WHERE F1.year = F2.year AND F2.year = F3.year AND F3.year = F4.year AND F1.code = 'e1' AND F2.code = 'e1' AND F3.code = 'e1' AND F4.code = 'e1' AND F1.quarter = 'Q1' AND F2.quarter = 'Q2' AND F3.quarter = 'Q3' AND F4.quarter = 'Q4'

  30. Possible Solution – Temporary Table One Step Using SubQuery INSERT INTO QAmt (year, code, Q1Amt, Q2Amt, Q3Amt, Q4Amt) SELECT year, code, amount, (SELECT amount FROM fin_data F WHERE F.year = Q.year AND F.code = Q.code AND F.quarter = 'Q2' ), (SELECT amount FROM fin_data F WHERE F.year = Q.year AND F.code = Q.code AND F.quarter = 'Q3' ), (SELECT amount FROM fin_data F WHERE F.year = Q.year AND F.code = Q.code AND F.quarter = 'Q4' ) FROM fin_data Q WHERE quarter = 'Q1' AND code = 'e1'

  31. Possible Solution – Temporary Table Problems So… using a temporary table really isn’t a distinct approach at all. It can make the solution conceptually simpler by allowing us to divide the solution into discrete steps, but it does nothing to reduce overall coding complexity or to improve performance. In addition, it introduces potential problems with regard to updates made between the time the table is created and the time it is used (currency). If you really do want a snapshot of the data at a particular time then this can be useful. In general, it’s undesirable You can overcome the currency problem by creating a view instead of a table but, so far, you’re still basically using either a self-join or a subquery method. If you want to create a View you’re better off using Characteristic Functions to do it! We still need a better solution!

  32. Better Solution Characteristic Functions

  33. Characteristic Function • <mathematics> The characteristic function of set returns True if its argument is an element of the set and False otherwise. http://www.cacs.louisiana.edu/~mgr/404/burks/foldoc/98/18.htm

More Related