मेरे पास एक सारणी है जो खरीदी गई वस्तुओं की सूची दिखाती है। तालिका से, मूल्य गणना प्राप्त करने के लिए तालिका प्रति वर्ष मूल्यह्रास दर दिखाती है। गणना से, मैं प्राप्त करना चाहता हूं कि वर्तमान तिथि की समाप्ति तिथि तक मूल्य वर्ष में कैसे बदलता है। तालिका इस तरह दिखती है

| item | bought_date | price  | depreciation_rate | end_date
| x    | 2018-01-12  | 5800   |  50               | 2020-01-17
| y    | 2015-06-01  | 9200   |  20               | 2020-01-17

मैंने इस डमी डेटा के साथ क्वेरी की है

WITH raw AS(
SELECT 'x' AS item
      , '2018-01-12' AS start_date
      , 5800 AS val
      , 50 AS dep_rate
      , CURRENT_DATE() AS end_date

UNION ALL

SELECT 'y'
       , '2015-06-01'
       , 9200
       , 20
       , CURRENT_DATE()
)

-- SELECT *
--   FROM raw

, gen_year AS(
SELECT item
    , DATE_TRUNC(year, MONTH) AS year
FROM raw
, UNNEST(GENERATE_DATE_ARRAY(PARSE_DATE('%Y-%m-%d',start_date), CURRENT_DATE(), INTERVAL 1 YEAR)) AS year
)

SELECT item
       , year
       , test
       , dep_rate
       , COALESCE(val, LAG(val) OVER(PARTITION BY item ORDER BY year) - LAG(dep_val) OVER(PARTITION BY item ORDER BY year)) AS val
       , COALESCE(dep_val, COALESCE(val, LAG(val) OVER(PARTITION BY item ORDER BY year) - LAG(dep_val) OVER(PARTITION BY item ORDER BY year)) *test/12*50/100) AS dep_val
  FROM (
        SELECT *
               , test/12 * dep_rate/100 * val AS dep_val
          FROM (
                SELECT g.item
                    , g.year
                    , 12 AS test
                    , r.val
                    , LAST_VALUE(r.dep_rate IGNORE NULLS) OVER (PARTITION BY g.item ORDER BY year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS dep_rate
                FROM gen_year AS g
                    LEFT JOIN raw AS r ON r.item = g.item
                                    AND DATE_TRUNC(PARSE_DATE('%Y-%m-%d',r.start_date), MONTH) = year
                )
          )

उपरोक्त क्वेरी केवल 1 निम्न पंक्ति पर शून्य मान भरें, शेष शून्य मान नहीं

item |  year     | test | dep_rate |val     |   dep_val 
 x   |2018-01-01 |   12 |    50    | 5800.0 |    2900.0
 x   |2019-01-01 |   12 |    50    | 2900.0 |    1450.0
 x   |2020-01-01 |   12 |    50    |  null  |    null
 y   |2015-06-01 |   12 |    20    | 9200.0 |   1840.0
 y   |2016-06-01 |   12 |    20    | 7360.0 |   3680.0
 y   |2017-06-01 |   12 |    20    |  null  |    null
 y   |2018-06-01 |   12 |    20    |  null  |    null
 y   |2019-06-01 |   12 |    20    |  null  |    null

Dep_val गणना है

वैल * dep_rate/100 * टेस्ट/12

मैं इस गणना के साथ शून्य मान भरना चाहता हूं ..

पिछली पंक्ति पर वैल - पिछली पंक्ति पर dep_val

मैं जिस परिणाम की तलाश कर रहा हूं वह इस तरह है

item |  year     | test | dep_rate |val     |   dep_val 
 x   |2018-01-01 |   12 |    50    | 5800.0 |    2900.0
 x   |2019-01-01 |   12 |    50    | 2900.0 |    1450.0
 x   |2020-01-01 |   12 |    50    | 1450.0  |    725
 y   |2015-06-01 |   12 |    20    | 9200.0 |   1840.0
 y   |2016-06-01 |   12 |    20    | 7360.0 |   1472
 y   |2017-06-01 |   12 |    20    |  5888  |    1177
 y   |2018-06-01 |   12 |    20    |  4711  |    942.2
 y   |2019-06-01 |   12 |    20    |  3768  |    753.76
0
Adiansyah 17 जिंदा 2020, 18:43

2 जवाब

सबसे बढ़िया उत्तर

नीचे BigQuery Standard SQL के लिए है और अंत में काफी सरल है ...

#standardSQL
SELECT item,  
  DATE_TRUNC(DATE_ADD(bought_date, INTERVAL index YEAR), MONTH) year, 
  depreciation_rate,
  ROUND(price * POW((1 - depreciation_rate / 100), index), 2) AS val,
  ROUND(price * POW((1 - depreciation_rate / 100), index) * depreciation_rate / 100, 2) AS dep_val
FROM `project.dataset.table`,
UNNEST(GENERATE_ARRAY(0, DATE_DIFF(end_date, bought_date, YEAR))) AS index

आप नीचे दिए गए उदाहरण के अनुसार अपने प्रश्न से नमूना डेटा का उपयोग करके परीक्षण कर सकते हैं, ऊपर के साथ खेल सकते हैं:

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'x' item, DATE '2018-01-12' bought_date, 5800 price, 50 depreciation_rate, DATE '2020-01-17' end_date UNION ALL
  SELECT 'y', '2015-06-01', 9200, 20, '2020-01-17' 
)
SELECT item,  
  DATE_TRUNC(DATE_ADD(bought_date, INTERVAL index YEAR), MONTH) year, 
  depreciation_rate,
  ROUND(price * POW((1 - depreciation_rate / 100), index), 2) AS val,
  ROUND(price * POW((1 - depreciation_rate / 100), index) * depreciation_rate / 100, 2) AS dep_val
FROM `project.dataset.table`,
UNNEST(GENERATE_ARRAY(0, DATE_DIFF(end_date, bought_date, YEAR))) AS index
-- ORDER BY item, year   

परिणाम के साथ

Row item    year            depreciation_rate   val         dep_val  
1   x       2018-01-01      50                  5800.0      2900.0   
2   x       2019-01-01      50                  2900.0      1450.0   
3   x       2020-01-01      50                  1450.0       725.0   
4   y       2015-06-01      20                  9200.0      1840.0   
5   y       2016-06-01      20                  7360.0      1472.0   
6   y       2017-06-01      20                  5888.0      1177.6   
7   y       2018-06-01      20                  4710.4       942.08  
8   y       2019-06-01      20                  3768.32      753.66  
9   y       2020-06-01      20                  3014.66      602.93  
1
Mikhail Berlyant 18 जिंदा 2020, 00:06

कुछ राउंड-ए-बाउट समाधान के लिए मेरी क्वेरी नीचे देखें।
मुख्य आइटम sum(log(1-dep_rate/100)) over (partition by item order by year) है, जो एक संचयी 'लॉग फैक्टर' उत्पन्न करेगा, जिसे आप तब प्रतिपादित करेंगे और प्रत्येक वर्ष के लिए मूल्य प्राप्त करने के लिए मूल मान से गुणा करेंगे।

WITH raw AS(
  SELECT 'x' AS item, '2018-01-12' AS start_date, 5800 AS val, 50 AS dep_rate, CURRENT_DATE() AS end_date
  UNION ALL
  SELECT 'y', '2015-06-01', 9200, 20, CURRENT_DATE()
),
gen_year AS(
  SELECT item, DATE_TRUNC(year, MONTH) AS year
  FROM raw, UNNEST(GENERATE_DATE_ARRAY(PARSE_DATE('%Y-%m-%d',start_date), CURRENT_DATE(), INTERVAL 1 YEAR)) AS year
),
temp as (
  select item, year, dep_rate, val, sum(log(1-dep_rate/100)) over (partition by item order by year asc) as log_factor
  from gen_year
  left join raw using(item)
),
temp2 as (
  select *, val * exp(log_factor) as dep_val, row_number() over (partition by item order by year asc) as rn from temp
),
temp3 as (
  select *, lag(dep_val,1) over (partition by item order by year asc) as lagged_dep_val from temp2
),
temp4 as (
  select item,year,dep_rate, case when rn = 1 then val else lagged_dep_val end as val, dep_val from temp3
)
select item, year, dep_rate, round(val,2), round(val-dep_val,2) as dep_val from temp4
1
rtenha 17 जिंदा 2020, 19:56