| ==== |
| ---- QUERY: TPCH-Q9 |
| # Q9 - Product Type Measure Query |
| select |
| nation, |
| o_year, |
| sum(amount) as sum_profit |
| from( |
| select |
| n_name as nation, |
| year(o_orderdate) as o_year, |
| l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount |
| from |
| customer.c_orders o, |
| o.o_lineitems l, |
| supplier s, |
| s.s_partsupps ps, |
| part p, |
| region.r_nations n |
| where |
| s_suppkey = l_suppkey |
| and ps_partkey = l_partkey |
| and p_partkey = l_partkey |
| and s_nationkey = n_nationkey |
| and p_name like '%green%' |
| ) as profit |
| group by |
| nation, |
| o_year |
| order by |
| nation, |
| o_year desc |
| ---- RESULTS |
| 'ALGERIA',1998,27136900.1803 |
| 'ALGERIA',1997,48611833.4962 |
| 'ALGERIA',1996,48285482.6782 |
| 'ALGERIA',1995,44402273.5999 |
| 'ALGERIA',1994,48694008.0668 |
| 'ALGERIA',1993,46044207.7838 |
| 'ALGERIA',1992,45636849.4881 |
| 'ARGENTINA',1998,28341663.7848 |
| 'ARGENTINA',1997,47143964.1176 |
| 'ARGENTINA',1996,45255278.6021 |
| 'ARGENTINA',1995,45631769.2054 |
| 'ARGENTINA',1994,48268856.3547 |
| 'ARGENTINA',1993,48605593.6162 |
| 'ARGENTINA',1992,46654240.7487 |
| 'BRAZIL',1998,26527736.3960 |
| 'BRAZIL',1997,45640660.7677 |
| 'BRAZIL',1996,45090647.1630 |
| 'BRAZIL',1995,44015888.5132 |
| 'BRAZIL',1994,44854218.8932 |
| 'BRAZIL',1993,45766603.7379 |
| 'BRAZIL',1992,45280216.8027 |
| 'CANADA',1998,26828985.3944 |
| 'CANADA',1997,44849954.3186 |
| 'CANADA',1996,46307936.1108 |
| 'CANADA',1995,47311993.0441 |
| 'CANADA',1994,46691491.9596 |
| 'CANADA',1993,46634791.1121 |
| 'CANADA',1992,45873849.6882 |
| 'CHINA',1998,27510180.1657 |
| 'CHINA',1997,46123865.4097 |
| 'CHINA',1996,49532807.0601 |
| 'CHINA',1995,46734651.4838 |
| 'CHINA',1994,46397896.6097 |
| 'CHINA',1993,49634673.9463 |
| 'CHINA',1992,46949457.6426 |
| 'EGYPT',1998,28401491.7968 |
| 'EGYPT',1997,47674857.6783 |
| 'EGYPT',1996,47745727.5450 |
| 'EGYPT',1995,45897160.6783 |
| 'EGYPT',1994,47194895.2280 |
| 'EGYPT',1993,49133627.6471 |
| 'EGYPT',1992,47000574.5027 |
| 'ETHIOPIA',1998,25135046.1377 |
| 'ETHIOPIA',1997,43010596.0838 |
| 'ETHIOPIA',1996,43636287.1922 |
| 'ETHIOPIA',1995,43575757.3343 |
| 'ETHIOPIA',1994,41597208.5283 |
| 'ETHIOPIA',1993,42622804.1616 |
| 'ETHIOPIA',1992,44385735.6813 |
| 'FRANCE',1998,26210392.2804 |
| 'FRANCE',1997,42392969.4731 |
| 'FRANCE',1996,43306317.9749 |
| 'FRANCE',1995,46377408.4328 |
| 'FRANCE',1994,43447352.9922 |
| 'FRANCE',1993,43729961.0639 |
| 'FRANCE',1992,44052308.4290 |
| 'GERMANY',1998,25991257.1071 |
| 'GERMANY',1997,43968355.8079 |
| 'GERMANY',1996,45882074.8049 |
| 'GERMANY',1995,43314338.3077 |
| 'GERMANY',1994,44616995.4369 |
| 'GERMANY',1993,45126645.9113 |
| 'GERMANY',1992,44361141.2107 |
| 'INDIA',1998,29626417.2379 |
| 'INDIA',1997,51386111.3448 |
| 'INDIA',1996,47571018.5122 |
| 'INDIA',1995,49344062.2829 |
| 'INDIA',1994,50106952.4261 |
| 'INDIA',1993,48112766.6987 |
| 'INDIA',1992,47914303.1234 |
| 'INDONESIA',1998,27734909.6763 |
| 'INDONESIA',1997,44593812.9863 |
| 'INDONESIA',1996,44746729.8078 |
| 'INDONESIA',1995,45593622.6993 |
| 'INDONESIA',1994,45988483.8772 |
| 'INDONESIA',1993,46147963.7895 |
| 'INDONESIA',1992,45185777.0688 |
| 'IRAN',1998,26661608.9301 |
| 'IRAN',1997,45019114.1696 |
| 'IRAN',1996,45891397.0992 |
| 'IRAN',1995,44414285.2348 |
| 'IRAN',1994,43696360.4795 |
| 'IRAN',1993,45362775.8094 |
| 'IRAN',1992,43052338.4143 |
| 'IRAQ',1998,31188498.1914 |
| 'IRAQ',1997,48585307.5222 |
| 'IRAQ',1996,50036593.8404 |
| 'IRAQ',1995,48774801.7275 |
| 'IRAQ',1994,48795847.2310 |
| 'IRAQ',1993,47435691.5082 |
| 'IRAQ',1992,47562355.6571 |
| 'JAPAN',1998,24694102.1720 |
| 'JAPAN',1997,42377052.3454 |
| 'JAPAN',1996,40267778.9094 |
| 'JAPAN',1995,40925317.4650 |
| 'JAPAN',1994,41159518.3058 |
| 'JAPAN',1993,39589074.2771 |
| 'JAPAN',1992,39113493.9052 |
| 'JORDAN',1998,23489867.7893 |
| 'JORDAN',1997,41615962.6619 |
| 'JORDAN',1996,41860855.4684 |
| 'JORDAN',1995,39931672.0908 |
| 'JORDAN',1994,40707555.4638 |
| 'JORDAN',1993,39060405.4658 |
| 'JORDAN',1992,41657604.2684 |
| 'KENYA',1998,25566337.4303 |
| 'KENYA',1997,43108847.9024 |
| 'KENYA',1996,43482953.5430 |
| 'KENYA',1995,42517988.9814 |
| 'KENYA',1994,43612479.4523 |
| 'KENYA',1993,42724038.7571 |
| 'KENYA',1992,43217106.2068 |
| 'MOROCCO',1998,24915496.8756 |
| 'MOROCCO',1997,42698382.8550 |
| 'MOROCCO',1996,42986113.5049 |
| 'MOROCCO',1995,42316089.1593 |
| 'MOROCCO',1994,43458604.6029 |
| 'MOROCCO',1993,42672288.0699 |
| 'MOROCCO',1992,42800781.6415 |
| 'MOZAMBIQUE',1998,28279876.0301 |
| 'MOZAMBIQUE',1997,51159216.2298 |
| 'MOZAMBIQUE',1996,48072525.0645 |
| 'MOZAMBIQUE',1995,48905200.6007 |
| 'MOZAMBIQUE',1994,46092076.2805 |
| 'MOZAMBIQUE',1993,48555926.2669 |
| 'MOZAMBIQUE',1992,47809075.1192 |
| 'PERU',1998,26713966.2678 |
| 'PERU',1997,48324008.6011 |
| 'PERU',1996,50310008.8629 |
| 'PERU',1995,49647080.9629 |
| 'PERU',1994,46420910.2773 |
| 'PERU',1993,51536906.2487 |
| 'PERU',1992,47711665.3137 |
| 'ROMANIA',1998,27271993.1010 |
| 'ROMANIA',1997,45063059.1953 |
| 'ROMANIA',1996,47492335.0323 |
| 'ROMANIA',1995,45710636.2909 |
| 'ROMANIA',1994,46088041.1066 |
| 'ROMANIA',1993,47515092.5613 |
| 'ROMANIA',1992,44111439.8044 |
| 'RUSSIA',1998,27935323.7271 |
| 'RUSSIA',1997,48222347.2924 |
| 'RUSSIA',1996,47553559.4932 |
| 'RUSSIA',1995,46755990.0976 |
| 'RUSSIA',1994,48000515.6191 |
| 'RUSSIA',1993,48569624.5082 |
| 'RUSSIA',1992,47672831.5329 |
| 'SAUDI ARABIA',1998,27113516.8424 |
| 'SAUDI ARABIA',1997,46690468.9649 |
| 'SAUDI ARABIA',1996,47775782.6670 |
| 'SAUDI ARABIA',1995,46657107.8287 |
| 'SAUDI ARABIA',1994,48181672.8100 |
| 'SAUDI ARABIA',1993,45692556.4438 |
| 'SAUDI ARABIA',1992,48924913.2717 |
| 'UNITED KINGDOM',1998,26366682.8786 |
| 'UNITED KINGDOM',1997,44518130.1851 |
| 'UNITED KINGDOM',1996,45539729.6166 |
| 'UNITED KINGDOM',1995,46845879.3390 |
| 'UNITED KINGDOM',1994,43081609.5737 |
| 'UNITED KINGDOM',1993,44770146.7555 |
| 'UNITED KINGDOM',1992,44123402.5484 |
| 'UNITED STATES',1998,27826593.6825 |
| 'UNITED STATES',1997,46638572.3648 |
| 'UNITED STATES',1996,46688280.5474 |
| 'UNITED STATES',1995,48951591.6156 |
| 'UNITED STATES',1994,45099092.0598 |
| 'UNITED STATES',1993,46181600.5278 |
| 'UNITED STATES',1992,46168214.0901 |
| 'VIETNAM',1998,27281931.0011 |
| 'VIETNAM',1997,48735914.1796 |
| 'VIETNAM',1996,47824595.9040 |
| 'VIETNAM',1995,48235135.8016 |
| 'VIETNAM',1994,47729256.3324 |
| 'VIETNAM',1993,45352676.8672 |
| 'VIETNAM',1992,47846355.6485 |
| ---- TYPES |
| STRING, INT, DECIMAL |
| ==== |
