// Query using only standard operations: private IDataList QueryOrdersCount() { // get OrderDetails table dynamic od = workspace.table(Utils.OrderDetails.Name); // aggregate OrderDetails table dynamic qp = workspace.query("query_ordersCount", new { Product = od.ProductID, // group by order Orders = Op.Count(od.OrderID) // how many orders are made for each product }); return(qp.Query.Execute()); }
// Query using only standard operations: // discount information for all products private IDataList QueryProductDiscount() { // get OrderDetails table dynamic od = workspace.table(Utils.OrderDetails.Name); // aggregate OrderDetails table dynamic qp = workspace.query("query_productDiscount", new { ds = Op.Mul(od.UnitPrice, od.Discount), // calculate discount for a product in an order Product = od.ProductID, // group by product OrderCount = Op.Count(od.OrderID), // order count for a product DiscountSum = Op.Sum("ds") // total discount for a product in all orders }); return(qp.Query.Execute()); }
// discount information for the specified product private IDataList QueryProduct() { int productID = cbProduct.SelectedIndex; // get OrderDetails table dynamic od = workspace.table(Utils.OrderDetails.Name); // aggregate OrderDetails table dynamic qp = workspace.query("query_product", new { _range = od.ProductID.Eq(productID), // narrow data to the specified product ds = Op.Mul(od.UnitPrice, od.Discount), // calculate the discount for a product in an order Product = od.ProductID, // group by product Discount = od.Discount, // and group by discount OrdersCount = Op.Count(od.OrderID), // order count for products with the same discount in all orders DiscountSum = Op.Sum("ds") // total discount for products with the same discount in all orders }); return(qp.Query.Execute()); }
// products within specified unit price range private IDataList QueryUnitPriceRange() { double from = (double)nudUnitPriceFrom.Value; double to = (double)nudUnitPriceTo.Value; // get OrderDetails table dynamic od = workspace.table(Utils.OrderDetails.Name); // aggregate OrderDetails table dynamic qr = workspace.query("query_range", new { _range = od.UnitPrice.Gte(from).Lte(to), // narrow data to the specified unit price range ds = Op.Mul(od.UnitPrice, od.Discount), // calculate the discount for a product in an order Price = od.UnitPrice, // group by UnitPrice OrdersCount = Op.Count(od.OrderID), // how many times products with the same price were ordered MaxDiscount = Op.Max("ds") // what is the maximum discount for products with the same price }); return(qr.Query.Execute()); }
// order information by month private IDataList QueryMonth() { // get Orders table dynamic orders = workspace.table(Utils.Orders.Name); // create a query from Orders table with additional OrdersMonth column dynamic qm = workspace.query("query_date", new { _base = "*", // all columns from the Orders table OrderMonth = Op.DtPart(orders.OrderDate, DateTimeParts.Month) // month from Orders.OrderDate column }); // aggregate query_date data dynamic qdt = workspace.query("query_month", new { Month = qm.OrderMonth, // group by OrderMonth FirstOrderDate = Op.Min(qm.OrderDate), // first order date in that month OrderCount = Op.Count(qm.OrderID) // order count in that month }); return(qdt.Query.Execute()); }
// products that were ordered more than specified number of times private IDataList QueryGroupRange() { // get OrderDetails table dynamic od = workspace.table(Utils.OrderDetails.Name); // aggregate OrderDetails dynamic qb = workspace.query("query_group", new { Product = od.ProductID, // group by ProducID OrdersCount = Op.Count(od.OrderID), // order count of that product SumPrice = Op.Sum(od.UnitPrice) // price sum of that product }); // filter products by order count long from = (long)nudGroupFilter.Value; dynamic qg = workspace.query("query_group_range", new { _base = "*", // include all columns from the aggregated OrderDetails _range = qb.OrdersCount.Gte(from).Lte(long.MaxValue) // filter products by order count }); return(qg.Query.Execute()); }
// product information: how many times it was ordered (from Order Details) and how many items are left in stock (from Products table) private IDataList QueryJoin() { // get tables dynamic od = workspace.table(Utils.OrderDetails.Name); dynamic pr = workspace.table(Utils.Products.Name); // join OrderDetails and Products tables dynamic jq = workspace.join("query_join", od, new { od = od.ProductID + od.OrderID, // get ProductID and OrderID from the OrderDetails table pr = pr.UnitsInStock | od.ProductID == pr.ProductID // get UnitsInStock from the Products table and join OrderDetails and Products table on ProductID field }); // aggregate joined data dynamic qjf = workspace.query("query_join_result", new { Product = jq.ProductID, // group by ProductID OrdersCount = Op.Count(jq.OrderID), // order count of a product UnitsOnStock = Op.First(jq.UnitsInStock), // how many units of a product left in stock }); return(qjf.Query.Execute()); }
static void Main(string[] args) { // Create and initialize a new workspace folder relative to the project root Workspace workspace = new Workspace(); workspace.KeepFiles = KeepFileType.Results; workspace.Init("workspace"); // Uncomment the following line to clear the workspace before each run workspace.Clear(); // Extract JSON data from a zip file, if not already done if (!File.Exists("seattle-pet-licenses.json") || !File.Exists("washington-zip-codes.json")) { ZipFile.ExtractToDirectory("data.zip", "."); } // Import the main license table if (!workspace.TableExists("PetLicenses")) { List <PetLicense> collection = JsonConvert.DeserializeObject <List <PetLicense> >(File.ReadAllText("seattle-pet-licenses.json")); ObjectConnector <PetLicense> connector = new ObjectConnector <PetLicense>(workspace, collection); connector.GetData("PetLicenses"); workspace.Save(); Console.WriteLine("{0:d} licenses imported.", collection.Count); } // Import the secondary location table if (!workspace.TableExists("Locations")) { List <Location> collection = JsonConvert.DeserializeObject <List <Location> >(File.ReadAllText("washington-zip-codes.json")); ObjectConnector <Location> connector = new ObjectConnector <Location>(workspace, collection); connector.GetData("Locations"); workspace.Save(); Console.WriteLine("{0:d} locations imported.", collection.Count); } // Retrieve the main table for use in constructing queries dynamic licenses = workspace.table("PetLicenses"); // Number of licenses, by species if (!workspace.QueryExists("BySpecies")) { dynamic query = workspace.query("BySpecies", new { licenses.Species, Count = Op.Count(licenses.Species) }); query.Query.Execute(); } // Number of licenses, by calendar year if (!workspace.QueryExists("ByYear")) { // Create a query with all base table columns and add a Year field, extracted from the DateTime value dynamic parent = workspace.query(new { _base = "*", Year = Op.DtPart(licenses.IssueDate, DateTimeParts.Year) }); // Derive another query from the unnamed query above and perform the aggregation dynamic query = workspace.query("ByYear", new { parent.Year, Count = Op.Count(parent.Year) }); query.Query.Execute(); } // Most popular dog names (sort criteria and row limits are applied later) if (!workspace.QueryExists("DogNames")) { // Use the _range attribute to limit the results to dogs only dynamic query = workspace.query("DogNames", new { _range = licenses.Species.Eq("Dog"), Species = licenses.Species, DogName = licenses.AnimalName, Count = Op.Count(licenses.AnimalName) }); query.Query.Execute(); } // List names for all species except dogs and cats if (!workspace.QueryExists("OtherAnimals")) { // Create a query with specified base table columns and use the _filter attribute to limit the results // (_filter is used instead of _range because the latter does not support the Ne operator) dynamic parent = workspace.query(new { _filter = licenses.Species.Ne("Dog").And().Ne("Cat"), licenses.LicenseNumber, licenses.Species, licenses.AnimalName }); // Derive another query from the unnamed query above and use First as the aggregation operator // (otherwise the query will not yield any results) dynamic query = workspace.query("OtherAnimals", new { parent.LicenseNumber, parent.Species, AnimalName = Op.First(parent.AnimalName) }); query.Query.Execute(); } // Retrieve the secondary table for use in a join query dynamic locations = workspace.table("Locations"); // Number of licenses in King County, by city if (!workspace.QueryExists("KingCounty")) { // Create a join from the main table (licenses) to the secondary table (locations) // * The addition expression to the left of the vertical bar denotes which secondary fields to include // * The equality expression to the right of the vertical bar specifies the join condition // * The assignment statement specifies a property of the anonymous type (its name is not significant) dynamic join = workspace.join(licenses, new { locale = locations.County + locations.City | licenses.ZipCode == locations.Zip }); // Derive another query from the join query above, specifying both a _range and an aggregation dynamic query = workspace.query("KingCounty", new { _range = join.County.Eq("King"), join.County, join.City, Count = Op.Count(join.LicenseNumber) }); query.Query.Execute(); } // Output query results in CSV format to the console IDataList species = workspace.GetQueryData("BySpecies"); Console.WriteLine(); Console.WriteLine("// Licenses issued, by species"); DataList.Write(species, Console.Out); IDataList years = workspace.GetQueryData("ByYear"); Console.WriteLine(); Console.WriteLine("// Licenses issued, by year"); DataList.Write(years, Console.Out); IDataList names = workspace.GetQueryData("DogNames", 10); // Limit results to 10 rows Console.WriteLine(); Console.WriteLine("// Most popular dog names"); DataList.Sort(names, "Count", false); // Sort by Count in descending order DataList.Write(names, Console.Out); IDataList others = workspace.GetQueryData("OtherAnimals"); Console.WriteLine(); Console.WriteLine("// Excluding dogs and cats"); DataList.Write(others, Console.Out); IDataList cities = workspace.GetQueryData("KingCounty"); Console.WriteLine(); Console.WriteLine("// Licenses issued in King County, by city"); DataList.Write(cities, Console.Out); }