Hive query for functions

@mystefied_ ,

Would you be able to describe in words what is the desired results of the query?

The query above doesn't make much sense to me but if you explain what you want to achieve I should be able to help you get the right query.

Cheers,

André

There is one row per date. to compare the difference over months, I was to sum the amount of each month, then find percentage change of the total price each month. 

I'm trying the date_trunc function, but it isnt working. 

@mystefied_ ,

Please try this:

select
  month,
  month_total,
  month_total / lag(month_total, 1) over (order by month) as percentage_over_previous_month,
  sum(month_total) over (order by month) as running_sum,
  sum(month_total) over (order by month) / sum(month_total) over (partition by 1) as running_percentage
from (
  select
    trunc(stock_date, 'MONTH') as month,
    sum(stock_price) as month_total
  from table4
  group by trunc(stock_date, 'MONTH')
) x

Cheers,

André

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.

@mystefied_ ,

Which version of Hive are you using? That query works well on my cluster.

Nevertheless, you should be able to run the below, which is pretty much the same:

select
  yr,
  mth,
  month_total,
  month_total / lag(month_total, 1) over (order by yr, mth) as percentage_over_previous_month,
  sum(month_total) over (order by yr, mth) as running_sum,
  sum(month_total) over (order by yr, mth) / sum(month_total) over (partition by 1) as running_percentage
from (
  select
    year(stock_date) as yr,
    month(stock_date) as mth,
    sum(stock_price) as month_total
  from table4
  group by year(stock_date), month(stock_date)
) x

Cheers,

André

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.

Woah! CDH 5.4.3 is *really* old. Unfortunatelly I don't have a cluster running that version here to test.

Hive has come a long way since then. We're already using Hive 3 on CDP 7.x.

I'd recommend you upgrade your system, if possible.

Cheers,

André

@mystefied_ ,

You can download the CDP Trial version from the Cloudera website below:

https://www.cloudera.com/downloads/cdp-private-cloud-trial/cdp-private-cloud-base-trial.html

Cheers,

André