DROP TABLE sentencepitch_fm; 
SELECT 
	translitphoneme.id, 
	max(pitch_fm.time2) as maximum, 
	min(pitch_fm.time2) as minimum,
	max(pitch_fm.time2) - min(pitch_fm.time2) as range,
	avg(pitch_fm.time2) as mean,
	(
   		SUM(pitch_fm.time2*pitch_fm.time2) - 
   		SUM(pitch_fm.time2) * SUM(pitch_fm.time2) / COUNT(pitch_fm.id)
	) as sd,
	max(12*ln(pitch_fm.time2)/ln(2.0)) as semitone_maximum, 
	min(12*ln(pitch_fm.time2)/ln(2.0)) as semitone_minimum,
	max(12*ln(pitch_fm.time2)/ln(2.0)) - min(12*ln(pitch_fm.time2)/ln(2.0)) as semitone_range,
	avg(12*ln(pitch_fm.time2)/ln(2.0)) as semitone_mean,
	(
   		SUM(12*ln(pitch_fm.time2)/ln(2.0)*12*ln(pitch_fm.time2)/ln(2.0)) - 
   		SUM(12*ln(pitch_fm.time2)/ln(2.0)) * SUM(12*ln(pitch_fm.time2)/ln(2.0)) / COUNT(pitch_fm.id)
	) as semitone_sd,
	count(pitch_fm.id) as number
INTO 
sentencepitch_fm
FROM 
	translitphoneme, 
	pitch_fm,
	phonemesphoneme 
WHERE 
	phonemesphoneme.manner in ('V', 'N', 'G')
	AND translitphoneme.id = phonemesphoneme.sentenceid 
	AND phonemesphoneme.id = pitch_fm.id 
GROUP BY 
	translitphoneme.id
;

GRANT SELECT ON sentencepitch_fm TO PUBLIC;
CREATE INDEX sentencepitch_fm_index on sentencepitch_fm (id);

UPDATE sentencepitch_fm SET 
sd  = SQRT(sd / (number - 1)),
semitone_sd  = SQRT(semitone_sd / (number - 1))
WHERE number > 1;
