SQL 小技巧

SELECT pow(2, log(2, sum((100 + change) / 100))), ticker FROM performance group by ticker

SELECT pow(2, log(2, sum((100 + change) / 100))), ticker FROM performance group by ticker

SELECT pow(2, sum(log(2,(100 + change) / 100))), ticker FROM performance group by ticker

create proc accumulate @startdate, @enddate
as
declare cursor c for select distinct ticker from perf

create temp table a

loop over c
  declare cursor c2 for select percent from perf where ticker = @ticker order by date
  loop over c2 and add up all percent for @ticker
    insert into a values(@ticker, @sum)

select * from a

select ticker, (exp(sum(log(1 + percent/100))) - 1) / 100 as accumulated_percent
from perf where date between startdate and enddate group by ticker

select ticker, count(*) as times into #five_star from perf
where star = 5 and date between startdate and enddate group by perf

select ticker, count(*) as times into #four_star from pef where star = 4 and date
between startdate and enddate group by perf

select ticker, count(*) as times into #three_star from perf where star = 3 and date
between startdate and enddate group by perf

select t.ticker, isnull(s5.times, 0) as star5, isnull(s4.times, 0) as star4, isnull(s3.times, 0) as star3
from ticker t
left join  #five_star s5 on t.ticker = s5.ticker
left join #four_star s4 on t.ticker = s4.ticker
left join #three_star s3 on t.ticker = s3.ticker
where isnull(s5.times, 0) + isnull(s4.times, 0) + isnull(s3.times, 0) >= 2

select ticker,
sum(case star when 5 then 1 else 0 end) star5, 
sum(case star when 4 then 1 else 0 end) star4,
sum(case star when 3 then 1 else 0 end) star3
from perf
where date between startdate and enddate
group by ticker
having sum(case when star >=3 then 1 else 0 end) >=2