Я использовал ColdFusion 2016 и ZingCharts (в комплекте) для динамического создания диаграмм с помощью SQL Server с временными рядами по оси X. Когда есть временные промежутки, я бы хотел, чтобы на линейной диаграмме также отображался промежуток, но вместо этого линия является непрерывной и отображает каждую точку данных последовательно.
Изображение диаграммы в том виде, в каком она построена сейчас, видно, что между датами 29 октября и марта нет «разрыва», данные просто объединяются:
Мои данные обычно делаются с шагом 15 минут, но есть промежутки времени (дни или месяцы), где есть пробелы в временных рядах и данных. Я связался с ZingCharts, чтобы спросить, есть ли какой-то тег стиля, который контролирует, отображаются ли даты последовательно или с пробелами, а его нет. Это то, чем нужно манипулировать на уровне данных. Если бы мои данные были жестко запрограммированы, мне пришлось бы добавить нулевые значения, чтобы диаграммы отображались с пробелами в временных рядах, но мои диаграммы являются динамическими (пользователь может выбрать любое количество из 7 параметров для добавления в диаграмму для выбранного диапазона дат. ). Я нашел информацию о том, как решить эту проблему для жестко закодированных данных, но я ищу идеи для решений для динамически загружаемых данных / серий. Я также нашел информацию об устаревшем теге coldfusion для XML-файла, isInterpolated="false"
, но это больше не вариант.
Мой вопрос в том, как лучше всего решить эту проблему? Я нашел некоторую информацию о создании календарной таблицы в SQL Server и объединении ее с таблицей (таблицами), предоставляющими данные, чтобы все даты были заполнены. Мне было интересно, есть ли другой подход, о котором я не думаю? Спасибо за любую помощь, я новичок во всем этом.
Обновление: вот текущий запрос данных, который немного сложен. Он извлекает "N-е" строки в зависимости от того, сколько параметров (доступно 7) выбрано и сколько дней находится в диапазоне дат:
SELECT
distinct
datepart(year, t.sample_date) as [year]
,datepart(month, t.sample_date) as [month]
,datepart(day, t.sample_date) as [day]
,datepart(hour, t.sample_time) as [hr]
,datepart(minute, t.sample_time) as [min]
,convert(varchar(10), t.sample_date, 1) + ' ' +
RIGHT('0' + CONVERT([varchar](2), DATEPART(HOUR, t.sample_time)), 2) + ':' +
RIGHT('0' + CONVERT([varchar](2), DATEPART(MINUTE, t.sample_time)), 2) AS [datetime]
,t.stationdesc
<cfif isDefined("form.parameter") and ListFindNoCase(form.parameter, "salinity")>,ROUND(t.salinity,1) as salinity</cfif>
<!---plus 6 more parameters--->
FROM (
SELECT
[sample_date]
,sample_time
,stationdesc
<cfif isDefined("form.parameter") and ListFindNoCase(form.parameter, "salinity") >,salinity</cfif>
<!---plus 6 more parameters--->
, row_number() OVER (ORDER BY streamcode) AS rownum
FROM MyUnionizedTables
WHERE stationdesc = (<cfqueryparam value="#form.station#" cfsqltype="cf_sql_varchar">)
AND [sample_date] BETWEEN (<cfqueryparam value='#Form.StartDate#' cfsqltype="cf_sql_date">)
AND (<cfqueryparam value='#Form.EndDate#' cfsqltype="cf_sql_date">)
<cfif isDefined("form.parameter") and ListFindNoCase(form.parameter, "salinity")>and salinity > -25 and salinity <40 and salinity is not NULL </cfif>
<!---plus 6 more parameters--->
GROUP BY sample_date, sample_time, stationdesc, streamcode
<cfif isDefined("form.parameter") and ListFindNoCase(form.parameter, "salinity")>,salinity</cfif>
<!---plus 6 more parameters--->
) AS t
WHERE <!---returning Nth row when record sets (count of days between dates selected) are long--->
<cfif IsDefined("form.station") AND IsDefined("form.parameter") AND #ParamCount# LTE 3 AND form.station eq 'Coastal Bays - Public Landing' and #ctdays# gte 10> t.rownum % 64 = 0
<cfelseif IsDefined("form.parameter") AND #ParamCount# LTE 3 AND #ctDays# gte '5840'> t.rownum % 64 = 0
<!---plus lots more elseifs--->
<cfelseif IsDefined("form.parameter") AND #ParamCount# GTE 7 AND #ctDays# gte '350'> t.rownum % 8 = 0
<cfelse>t.rownum % 1 = 0</cfif>
ORDER BY
datepart(year, t.sample_date)
,datepart(month, t.sample_date)
,datepart(day, t.sample_date)
,datepart(hour, t.sample_time)
,datepart(minute, t.sample_time)
ВТОРОЕ ОБНОВЛЕНИЕ (после ссылки Ли на запрос на GitHub):
Так что я на самом деле работал над запросом, аналогичным тому, который опубликовал Ли, на основе раздела «Выражение CTE» здесь. Я переключился на попытки работать с ее версией, которая находится ниже. У меня нет правок записи, поэтому я работаю с существующей таблицей. MyDataTable имеет ~ 21 мил строк, с отдельными sample_date (datetime) и sample_time (datetime) [даты и время являются PITA - b / c инструментов и способом удаленной телеметрии этих данных, мы получаем столбец datetime с 'хорошо date », но фиктивное значение времени, которое мы называем« sample_date », а затем отдельный столбец datetime с именем« sample_time »с фиктивной датой и« хорошим временем ».] Есть 125 станций, каждая с данными (например, температура) из разные даты / время начала и окончания, с 2001 г. по настоящее время. Поэтому мне нужно заполнить промежутки даты / времени для 125 различных станций с разными промежутками времени, которые обычно делаются с шагом 15 минут.
--- simulate main table(s)
--CREATE TABLE MyDataTable ( sample_date datetime, sample_time datetime, stationdesc nvarchar, wtemp float)
--- generate all dates within this range
DECLARE @startDate datetime
DECLARE @maxDate datetime
SET @startDate = '2015-01-01'
SET @maxDate = '2016-12-31'
--- get MISSING dates
;WITH missingDates AS
(
SELECT DATEADD(day,1,@startDate) AS TheDate
UNION ALL
SELECT DATEADD(day,1, TheDate)
FROM missingDates
WHERE TheDate < @maxDate
)
SELECT *
--[wtemp]
-- ,[stationdesc]
-- ,[TIMEVALUE]
FROM missingDates mi LEFT JOIN MyDataTable t ON t.sample_date = mi.TheDate
WHERE t.sample_date IS NULL
--and stationdesc = 'Back River - Lynch Point'
--ORDER BY timevalue
OPTION (MAXRECURSION 0)
Когда я запускаю этот запрос как есть, я получаю только 17 строк данных. В столбце «Дата» указаны даты и время с датами 15–12/16/12 и всегда 00: 00: 00.000. Запрос занимает 49 секунд.
Тем временем мы с коллегой работаем над альтернативными методами.
--Putting data from only 1 station from our big datatable into the new testtable called '_testdatatable'
SELECT station, sample_date, sample_time, wtemp, streamcode, stationdesc, TIMEVALUE
INTO _testdatatable
FROM MyBigDataTable
WHERE (stationdesc = 'Back River')
order by [sample_date],[sample_time]
--Next, make a new table [_testdatatableGap] with all time values in 15min increments from a datetime table we made
SELECT [wtemp]=null
,[streamcode]='ABC1234'
,[stationdesc]= 'Back River'
,[TIMEVALUE]
into [tide].[dbo].[_testdatatableGap]
FROM DateTimeTable
WHERE (TIMEVALUE BETWEEN '4/19/2014' AND getdate())
--Then, get the missing dates from the Gap table and put into the testdatatable
INSERT into [_testdatatable]
( [wtemp]
,[streamcode]
,[stationdesc]
,[TIMEVALUE]
)
(SELECT
[wtemp]=null -- needs this for except to work
,
[streamcode]
,[stationdesc]
,
[TIMEVALUE]
FROM [_testdatatableGap]
EXCEPT
SELECT
[wtemp]=null -- needs this for except to work
,
[streamcode]
,[stationdesc]
,
[TIMEVALUE]
FROM [_testdatatable])
Этот метод работал для создания таблицы со всеми 15-минутными приращениями даты / времени, что привело к правильно нарисованной диаграмме (ниже). Однако мы не знаем, как масштабировать это до полной таблицы данных на 125 станций без создания нескольких таблиц.
null
значений для всех недостающих точек? : / Сколько данных вы обычно наносите на график? Как выглядит базовый запрос? - person Leigh   schedule 07.12.2016SELECT cte.SomeDate, ot.SomeValue FROM cte LEFT JOIN otherTable ot ON ot.SomeDate = cte.SomeDate
, т.е. дата всегда заполняется, а значения заканчиваются какnull
для отсутствующих дат. - person Leigh   schedule 07.12.2016[timevalue]
содержит и дату, и время, т.е. 29.05.2014 17:15:00, то фильтрация выполняется только по дате, например, 29.05.2014 'будет исключать большинство записей за этот день. Обычно я использую подход в конце этой ветки, вместоbetween
, потому что он работает как со столбцами даты, так и со столбцами даты и времени. - person Leigh   schedule 22.12.2016